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

I would be interested to learn more about how this will be applied.

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.

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

(that would be equivalent to my case) – jandersson Jul 23 '09 at 13:18 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 Jul 23 '09 at 13:23 @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 Jul 23 '09 at 13:27 Andrew: Wouldn't that introduce NULLs in the Child table? Since the relationship between Parent2 and Child is optional.. – jandersson Jul 23 '09 at 13:31 nulls are good enough for Codd, they're good enough for you, too ;-) – Steven A. Lowe Jul 23 '097 at 0:04.

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.

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 Jul 24 '09 at 8:19 student(id,name) course(course#,name) dorm(dorm#,name) receipt(receipt#,amount,payment_mode, date_of_payment) dorm_receipt(dorm#,receipt#) course_receipt(course#,receipt#) 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 Jul 25 '09 at 16:23 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 Jul 26 '09 at 3:01.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.