The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

DB Design wats prefered in relational model

Hello,

What's the correct way to do stuff like this in the Relational model?

Example

Person
 Code Name PositionCode
Position
 Code Title

or

Person
 Code Name
Position
 Code Title
PersonPosition
  PersonCode
  PositionCode

If a Person could have more than one Position, the second method would be necessary. The same if Pos. Tracking was necessary. But if a Person can only have a Position at a given time, should we use the first way or the second way. Why?

Thanks.
q
Friday, August 10, 2007
 
 
You should obviously always use the second form because it makes better use of the relational many-to-many database feature. Besides, using the first form is not fully normalized which means it will lead to decreased performance and eventual database corruption.

Good luck with the rest of your homework.
r
Friday, August 10, 2007
 
 
Homework in the summer? No summer school here.

The *example* is stupid but it's not "homework".

Thanks.
q
Friday, August 10, 2007
 
 
r's response is not generally correct.  A separate "linking table" is *not* preferred unless the relationship is many-to-many.  If the relationship is one-to-many, then a simple foreign key column is most appropriate and universally used.
Ryan Send private email
Friday, August 10, 2007
 
 
Ryan is correct - the database structure should support the states possible in the situation being represented and should constrain the data so that it can't deviate from the possible states. If a person can only have one position then only two tables are required. A link table would only be required if you had to support something like periods of validity for the person-psition relationship in which case you'd have something like a date in there also.
David Aldridge Send private email
Friday, August 10, 2007
 
 
What about normalization in the first option?

Thanks.
q
Friday, August 10, 2007
 
 
Careful with the first example if it's possible the Person has no Position. null values in foreign key columns should be avoided.
Dave Send private email
Monday, August 13, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz