> > I'd probably re-think your main tables first. It looks like these should > be incorporated into one table because shows, mus numbers, etc all describe > one thing.
I simplified my descripition as the relationships are reasonably involved and it's an ass to describe dbs in text! The tables must absolutely be there own entites as they have lots of relationships between each other, i.e. 1 Show has many Musical Numbers 1 Show has many Creatives 1 Creative Type has many creatives (there are lots) 1 Creative has many Shows 1 Show has many Productions 1 Production has many actors 1 Actor has many productions 1 Show has many characters 1 Actor in a production has many characters 1 Character has many Musical Numbers 1 Musical Number has many characters etc. etc. !!! To add to the complexity, a user is also an actor - there is no actor table. So back to user lists; I think to keep things normalised I would need a seperate List table for each entity that I wanted listable. So for musical numbers: MusicalNumberList ---------------------------- MusicalNumberListID (PK) UserID (FK - user who owns the list) Title MusicalNumberListItem ---------------------------------- MusicalNumberListItemID (PK) MusicalNumberListID (FK) MusicalNumberID (FK) Ordinal The trouble with that is that makes for lots of tables and there are plenty already (add theatres, locations, reviews........). The up side is that the relationships are very clear. You want all the musical number lists for a user, no problem. There is a way using three tables only but it is anything but normalised: ListType ------------- ListTypeID (PK) TableName List ------ ListID (PK) ListTypeID (FK) UserID (FK) Title ListItem ----------- ListItemID (PK) ListID (FK) FK_Value (FK to a different table depending on listType) - eugh, either that or hav a nullable foreign key for each entity, stil eugh Ordinal I hope that better describes the dilemma. Really, I prefer the first method just because I hate the thought of code having to figure out which foreign key table to lookup! Thoughts? Something I'm missing? Dominic ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296398 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
