[SQL] many-to-many relationship
Hi, Say you have several objects (tables): person, location, event, etc. all of which can have several images attached. What is the best way to manage relations between a single 'image' table and these different objects? For now each 'image' row has pointers to id_person, id_location, id_event, etc. (only one of which is used for any given row). Is there a better way, more elegant way to do it, without using redundant id_* pointers on each row and yet still enforce foreign keys? Thanks, -- http://www.lesculturelles.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] many-to-many relationship
Louis-David Mitterrand wrote: Hi, Say you have several objects (tables): person, location, event, etc. all of which can have several images attached. What is the best way to manage relations between a single 'image' table and these different objects? For now each 'image' row has pointers to id_person, id_location, id_event, etc. (only one of which is used for any given row). Is there a better way, more elegant way to do it, without using redundant id_* pointers on each row and yet still enforce foreign keys? The typical way to do this would be to have your image table be just about images, and then to isolate the relationship information into mapping tables. Those would look like: image <=> people (image_id, person_id), with the primary key being the pair of columns. In SQL, roughly: create table image_people_map ( image_id integer not null, person_id integer not null, primary key (image_id, person_id) ); Similarly, for locations it'd be (image_id, location_id), and for events (image_id, event_id). Then you can have a single image associated with any number of people, events, or locations. Regards, -- Dave Steinberg http://www.geekisp.com/ http://www.steinbergcomputing.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] many-to-many relationship
On Mon, Oct 06, 2008 at 09:25:09AM -0400, Dan McFadyen wrote: > Hello, > > Simplest way I can think of is create 3 relation tables, a person/image > table, location/image table and event/image table. > > Each is just made up for 2 foreign keys to the first ID and image ID, > using both as the PK for the table. On Mon, Oct 06, 2008 at 09:30:41AM -0400, Dave Steinberg wrote: > The typical way to do this would be to have your image table be just > about images, and then to isolate the relationship information into > mapping tables. Those would look like: > > image <=> people > (image_id, person_id), with the primary key being the pair of columns. > In SQL, roughly: Thanks Dan and Dave, you suggested the same solution which seems the most reasonable. -- http://www.lesculturelles.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_dump more than one table in one command?
Good morning, Under postgreSQL 8.0, could someone tell me how to pg_dump more than one tables at the same time please? I tried to do something like: pg_dump -h machineName -U username --inserts --column-inserts --file=dump.sql --table=t1 t2 ... ...tN -d databaseName ; Thanks a lot! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump more than one table in one command?
You must specify --table (or -t) once for each of the tables, ie: pg_dump -h machineName -U username --inserts --column-inserts --file=dump.sql --table=t1 --table=t2 . . --table=tN -d databaseName ; -t t1 -t t2 -t t3 On Mon, Oct 6, 2008 at 5:02 PM, Emi Lu <[EMAIL PROTECTED]> wrote: > Good morning, > > Under postgreSQL 8.0, could someone tell me how to pg_dump more than one > tables at the same time please? > > I tried to do something like: > > pg_dump -h machineName -U username --inserts --column-inserts > --file=dump.sql --table=t1 t2 ... ...tN -d databaseName ; > > Thanks a lot! > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] pg_dump more than one table in one command?
On Mon, Oct 6, 2008 at 3:24 PM, Harold A. Giménez Ch. <[EMAIL PROTECTED]> wrote: > > On Mon, Oct 6, 2008 at 5:02 PM, Emi Lu <[EMAIL PROTECTED]> wrote: >> >> Good morning, >> >> Under postgreSQL 8.0, could someone tell me how to pg_dump more than one >> tables at the same time please? >> >> I tried to do something like: >> >> pg_dump -h machineName -U username --inserts --column-inserts >> --file=dump.sql --table=t1 t2 ... ...tN -d databaseName ; > You must specify --table (or -t) once for each of the tables, ie: > pg_dump -h machineName -U username --inserts --column-inserts > --file=dump.sql --table=t1 --table=t2 . . --table=tN -d databaseName > ; > > -t t1 -t t2 -t t3 I'm pretty sure that the ability to handle multiple -t switches was an 8.2 release feature. See the release notes (search for -t) http://www.postgresql.org/docs/8.3/static/release-8-2.html -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump more than one table in one command?
Looks like you are right, Scott. Thanks! I wasn't in Postgres land back then. On Mon, Oct 6, 2008 at 5:48 PM, Scott Marlowe <[EMAIL PROTECTED]>wrote: > On Mon, Oct 6, 2008 at 3:24 PM, Harold A. Giménez Ch. > <[EMAIL PROTECTED]> wrote: > > > > On Mon, Oct 6, 2008 at 5:02 PM, Emi Lu <[EMAIL PROTECTED]> wrote: > >> > >> Good morning, > >> > >> Under postgreSQL 8.0, could someone tell me how to pg_dump more than one > >> tables at the same time please? > >> > >> I tried to do something like: > >> > >> pg_dump -h machineName -U username --inserts --column-inserts > >> --file=dump.sql --table=t1 t2 ... ...tN -d databaseName ; > > You must specify --table (or -t) once for each of the tables, ie: > > pg_dump -h machineName -U username --inserts --column-inserts > > --file=dump.sql --table=t1 --table=t2 . . --table=tN -d > databaseName > > ; > > > > -t t1 -t t2 -t t3 > > I'm pretty sure that the ability to handle multiple -t switches was an > 8.2 release feature. See the release notes (search for -t) > > http://www.postgresql.org/docs/8.3/static/release-8-2.html >
