How to model optional one-to-many relationships in 1NF

Question

How can you model a ratio of zero, one, or many types without breaking the first normal form? That is, without storing any NULL values.

Separate relationship table? Is it “worth” adding a table, or is it excessive normalization?

Here is an example that fits my real case:

Say we have a college, with some free courses and some free dorms. Some of the courses and dorm rooms are indeed paid. And many of the courses and rooms have the same fee, although they are different courses / rooms.

So, we will have:

tblCourse
Id
Name

tblDormRoom
Id
Address

tblFee
Id
Amount

To simulate this, I decided to add two tables to store an optional one-to-many relationship.

tblCourseToFee
CourseId
FeeId

tblDormRoomToFee
DormRoomId
FeeId

, , , DormRoom .

n , 1NF:

tblFee
Id
CourseId (nullable)
DormRoomId (nullable)
Amount

, .

Answer 1

- " " ( , Stackoverflow).

, , , .

- ? , .

Answer 2

. andrew wikipedia , "no nulls in 1NF" - . nulls Codd, ;-)

---

() "", "" "" , .

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 Id
    AnotherField nvarchar(128) not null
    ...
)

Answer 3

Look at this problem semantically. Is it a triple bond or two binary relations? Simply put: Collecting a compilation

• for the course
• for the hostel
• for the hostel used to stay during the course attendance
  
  For the first two cases you need to have two child tables. If the three cases are true, you only need to have one intersection table between the course, the toll and the hostel. Many times, the presence or absence of NULL is very complex due to subtle design issues.