How to model optional one-to-many relationship in 1NF.

Question

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..

Answer 1

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.

Answer 2

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
    ...
)

Answer 3

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.