When I try to design relation database using MSSQL Server 2008, I find that the MSSQL Server 2008 allow designer can create two relationships with one direction from a table to other table (for example, I have two table A an B, I can create two relationship from table A to table B with key from table A map with some field in table B). Please let me know in the real-world, do we need some relationships between two tables like that?

Many Thanks,
John.

Actually it is not very clear for me your question but i try to help
if you mean two fields in table A map to the primary key in table B I can give u an example in retail
If an item is received in boxes but is sold as each we hv to make conversion table like this:
tableA: id, UOMReceived, UOMSold, Factor
TableB (unit of measurement) : UOMid, description
if you mean two different fields in table A mapped to 2 different field in tableB (both must be unique) here is an example:
for example item table in retail can have id (as internal code number) and ISBN (standard numbering used especially for books)
so all table concerning table could be indexed by eihter id or ISBN which map to MasterItem
i hope it explain

hi John

A single one-to-many relationship between entity A and B can be expressed by (crowfoot notation) A ------< B. That means the primary key of A is foreign key in B. If there simultaneously exists also a one-to-many relationship between entity B and A like B ------< A the relationship between A and B is many-to-many, like A ---< R >--- B.

In real world many-to-many relationships are quite usual, just consider entities student and course. A Student can attend more than one course, therefore we have student ----< course. A course usually is attended by more than one student, therefore we have course -----< student. Both one-to-many relationships make a many-to-many relationship: student ---< attend >--- course. Mapping this entity-relationship model to relational database we obtain three tables: student, course and a linking table attend, where the primary key of table attend must be form from the primary keys of table student and course.

-- tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.