pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
relationship/table design question 2+ messages / 2 participants [nested] [flat]
* relationship/table design question @ 2003-12-03 02:45 Taylor Lewick <tlewick@kc.rr.com> 2003-12-04 21:17 ` Re: relationship/table design question Jamie Lawrence <postgres@jal.org> 0 siblings, 1 reply; 2+ messages in thread From: Taylor Lewick @ 2003-12-03 02:45 UTC (permalink / raw) To: pgsql-sql Hi all, new to postgresql and I have a question about how to lay out my database. I have a database with 4 main tables, organizations, contacts, events, grants. My thinking here is that organizations can have contacts, sponsor events, and sponsor grants, so it is what I would call the main table. I know how to set up the other tables so that they have a reference back to org_id. (Basically a foreign key). How would I handle it (set up in SQL) if I want to be able to assign a contact to an event. In the event table, would I make a reference back to the contact table? Can I have more than one foreign key reference per table going to seperate tables? Finally, can I make the reference constraint be able to be NULL. Meaning, can I make it so that an event can, but doesn't have to have a contact assigned to it? The tables (a simplified view) would look like this... Organization Table: org_id org_name Contact Table: contact_id contact_name Event Table: event_id event_name reference back to org_id in org table reference back to contact_id in contact_table but there doesn't have to be a contact associated with this event. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: relationship/table design question 2003-12-03 02:45 relationship/table design question Taylor Lewick <tlewick@kc.rr.com> @ 2003-12-04 21:17 ` Jamie Lawrence <postgres@jal.org> 0 siblings, 0 replies; 2+ messages in thread From: Jamie Lawrence @ 2003-12-04 21:17 UTC (permalink / raw) To: Taylor Lewick <tlewick@kc.rr.com>; +Cc: pgsql-sql On Tue, 02 Dec 2003, Taylor Lewick wrote: > Hi all, new to postgresql and I have a question about how to lay out my > database. > > I have a database with 4 main tables, organizations, contacts, events, > grants. My thinking here is that organizations can have contacts, sponsor > events, > and sponsor grants, so it is what I would call > the main table. I know how to set up the other tables so that they have a > reference back to org_id. (Basically a foreign key). > > How would I handle it (set up in SQL) if I want to be able to assign a > contact to an event. In the event table, would I make a reference back to > the contact table? Can I have more than one foreign key reference per table Probably not what you want to do. > going to seperate tables? Finally, can I make the reference constraint be No, but see below. (Well, you could using an array or a marshalled field, but I can't imagine why you'd want to.) > able to be NULL. Meaning, can I make it so that an event can, but doesn't > have to have a contact assigned to it? Yes. > The tables (a simplified view) would look like this... > > Organization Table: > org_id > org_name > > Contact Table: > contact_id > contact_name > > Event Table: > event_id > event_name > reference back to org_id in org table > reference back to contact_id in contact_table but there doesn't have to be a > contact associated with this event. Hi there - In general, I would do something along these lines: create table organizations ( id serial primary key, organization text ); create table contacts ( id serial primary key, name text ); create table events ( id serial primary key, name text); create table events_contacts ( id serial primary key, events_id int references events, contacts_id int references contacts ); In order to associate a contact with an event, you insert a row in events_contacts with the appropriate ids of the event and the contact. Some people call this a join table, others a mapping table, others [insert 15 other names for many to many relations]. You'll have to decide how updates and deletes should behave for your purposes, and add in those clauses to the foreign key declarations. HTH, -j -- Jamie Lawrence jal@jal.org The bureaucracy is expanding to meet the needs of an expanding bureaucracy. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-12-04 21:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-12-03 02:45 relationship/table design question Taylor Lewick <tlewick@kc.rr.com> 2003-12-04 21:17 ` Jamie Lawrence <postgres@jal.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox