On Thu, 2004-05-20 at 07:13, Lewick, Taylor wrote: > Hi all, I am having a little trouble deciding how to relate some of my > tables together. > > > > I was wondering about creating one big lookup table to relate 3 or 4 > tables together, but wasn't sure if that was a good id, or should I have > a look up table > > For each pair of tables. > > > > Here is a simple example of my tables. > > > > Orgs: org_id org_name (org_id is primary key) > > > > Contacts: con_id, con_name, org_id (con_id is primary, org_id is > foreign key) > > > > Events: ev_id, ev_name, org_id, con_id (ev_id is primary, con_id and > org_id are foreign keys)
Its probably unnecessary to have a 'con_id' in the Event table since you can get that information from the 'org_id'. An exception might be if multiple contacts for one organization are allowed, and for any given event you want the ability to specify just one of those contacts. Removing the con_id would also help eliminate the problem where an org_id on an event does not agree with the org_id of the contact given by con_id (input or programming gremlin). Or, you have an existing event where the contact has the same org_id as the event's org_id, but there is a change of contacts at org_id's organization. Someone updates the contact information and now your event either contains an invalid contact, or an ID to a non-existent contact > This is centered around organizations, so every contact must belong to > an org, likewise for an event. I will create an org called None in case > they just want > > To track the occasional lone contact or internal event. > > > > But because an organization can have many contacts and many events, I > was thinking of using lookup tables. > > > > I.e., Contacts are assigned to Organizations, > > So have a table called assigned with org_id and con_id as a composite > primary key. And each is a foreign key back to the correct table... > > > > And should I have a table that links orgs and events and contacts and > events, or should I have one lookup table > > That relates them all together, i.e. orgs contacts, and events..? > > > > To simplify, is It better to have many smaller lookup tables or one big > one? > > > > Thanks, > > Taylor -- . Garth Webb . [EMAIL PROTECTED] . . shoes * éå * schoenen * ëí * chaussures * zapatos . Schuhe * ÏÎÏÎÏÏÏÎÎ * pattini * é * sapatas * ÐÐÑÐÐÐÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
