views:

322

answers:

3

How can you model a zero, one or many type relationship without breaking first normal form? That is, not storing any NULL values.

Separate relation table? Is it "worth" adding a table, or is this over-normalization?

Here's an example that corresponds to my real world case:

Say we have a college, with some free courses and some free dorm rooms. Some of the courses and dorm rooms do come with a fee though. And many of the courses and room does have the same fee (amount), although they are different courses/rooms.

So we'll have:

tblCourse
Id
Name

tblDormRoom
Id
Address

tblFee
Id
Amount

In order to model this my take was to add two tables to hold the optional one-to-many relationship.

tblCourseToFee
CourseId
FeeId

tblDormRoomToFee
DormRoomId
FeeId

This way I can avoid storing any null values, and also avoid duplicate storage of the Fee's that are shared between DormRoom and Course.

And the quick n dirtier version considered, that doesn't strictly adhere to 1NF:

tblFee
Id
CourseId (nullable)
DormRoomId (nullable)
Amount

This only uses one table instead of three, but introduces null values..

+1  A: 

This is an interesting question - a good example of a one-to-many relationship in first normal form can be found on Wikipedia (I would like to post some of the content here but it isn't Stackoverflow-format-friendly).

The basic idea is that you define two tables and the identifier of the main table is used as the identifier of the related table with the understanding that the related table may contain duplicate identifiers.

As a matter of curiosity are you working on some sort of data-mining project? I would be interested to learn more about how this will be applied.

Andrew Hare
+1  A: 

see andrew's link to wikipedia, and note that the 'no nulls in 1NF' admonition by Date is controverisal at best - not to mention frequently impractical. If nulls are good enough for Codd, they're good enough for me ;-)


that said, the normal (pun intended) way to model a zero, one, or many relationship is with a child table, e.g.

create table Parent (
    Id int not null identity(1),
    Somefield nvarchar(256) not null,
    ...
)

create table Child (
    Id int not null identity(1),
    ParentId int not null,    --foreign key to Parent table's Id
    AnotherField nvarchar(128) not null
    ...
)
Steven A. Lowe
What if you have a second Parent2 table, with the same optional one-to-many relationship to table Child? (that would be equivalent to my case)
jandersson
then you have a conditional relationship, which is not really handled very well in common relational databases. one solution is two mutually-exclusive nullable parentid fields, which will be a pain to maintain. another solution is two different child tables, ditto. another solution is a field to tell you the type/condition. if a child row may belong to both parents, a bridge table may make more sense. I'd suggest that you post the semantics of your entity model first, as this is a suspicious structure...
Steven A. Lowe
@jandersson - In that case you would create a second foreign-key relationship from Parent2 to Child by adding a Parent2Id field to the Child table. Steven's approach is classic 3NF and should be used for 99% of all situations. The only time the 1NF makes sense is for optimizing static records for fast reads.
Andrew Hare
Andrew: Wouldn't that introduce NULLs in the Child table? Since the relationship between Parent2 and Child is optional..
jandersson
nulls are good enough for Codd, they're good enough for you, too ;-)
Steven A. Lowe
A: 

Look at this problem semantically. Is it a ternary relationship or two binary relationships? In simple terms: Is the fee collected

  • for a course
  • for a dorm
  • for a dorm used to stay in while attending a course

    For the first two cases, you need to have the two child tables. If case three is true, you need to have just one intersection table between course, fee and dorm. Many times, the presence or absence of NULLs is very intricately tied to subtle design issues.
bkm
Two binary relationships, that is a student may either only rent a room, only take a course, or both. I can see how two child tables would work, although that would have the drawback of redundant storage of the fee data, for the cases where a room happens to have the same amount as a course. Do you have any comments on the solution of using two bridge tables and storing fee as a separate entity?
jandersson
student(id,name) <br/>course(course#,name) <br/>dorm(dorm#,name) <br/>receipt(receipt#,amount,payment_mode, date_of_payment)<br/>dorm_receipt(dorm#,receipt#) <br/>course_receipt(course#,receipt#) <br/>By moving the fee payment information into a separate table, you can avoid inconsistencies in the cases you mentioned above. This will also handle the case where a student makes a single payment for both the dorm and the course.
bkm
I would like to revise my earlier design. The current design can handle only two types of payments - student pays for a dorm or student pays for a course or both. You might have more scenarios in the future. You could have just one table for all payments like before: Receipt(receipt#,amount, payment_mode, date)Student_Payment(student_id, payment_code)Payment code can take values like 'Dorm', 'Course','Exam' etc...The fact that a student has enrolled for a course is different from the fact that a he/she has paid for it and can be modelld independently
bkm