One table or two?

From: Frostillicus <frosty_at_nilspamos.iinet.net.au>
Date: Wed, 18 Jan 2006 12:52:13 +1100
Message-ID: <43cd9f5c$0$23560$5a62ac22_at_per-qv1-newsreader-01.iinet.net.au>

I am, by no means, a database expert but I'm OK at normalising your average database requirements. My problem is as follows and deals with the field of classical music.

A symphony is made up of separate parts (movements) so I was thinking of having a one to many relationship (one symphony to many movements). However, I then wanted to be able to record the orchestrations for both the symphony in general but also for each separate movement (the symphony itself might use an ordinary orchestra, but a particular movement might have a special need, for a soprano, for example).

Should I include an orchestration_id field in both tables or would it be best to lump symphonies and movements into one table, adding a type_id field so that I can distinguish the parent from the child and another field so that movements can include the identifier of the parent symphony (and have a null or a zero if it's actually a symphony - feels kind of dodgy to me)?

This might be a clearer summary:

Option 1:

_tblSymphony_

symphony_id INTEGER
orchestration_id INTEGER

_tblMovement_

movement_id INTEGER
symphony_id INTEGER
orchestration_id INTEGER

_tblOrchestration_

orchestration_id INTEGER

Option 2:

_tblMusicItems_

music_item_id INTEGER

music_type_id  BIT                        (0 for symphonies, 1 for 

movements - maybe use CHAR instead, not sure yet?) parent_symphony_id INTEGER ("0" for symphonies, "n" for individual movements)
orchestration_id INTEGER

_tblMusicType_

music_type_id BIT
_tblOrchestration_

orchestration_id INTEGER

Thanks. Received on Wed Jan 18 2006 - 02:52:13 CET