Database Design & Relational tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Database Design & Relational tables

Post by arpowers »

hey everyone..

here is my question... when you are designing a relational database you have many different relations, is it better to assign a table to each 'type' of relation or to create a master relational table where all relations are defined.
For example:
rtbl_bananas_oranges
rtbl_bananas_pears

in contrast to:

rtbl_relations.. containing the columns

id(primarykey) .. object1_id.. object2_id

then go into object_id table and see what type it is..

....

the reason I ask is it gets tedious to keep track of an object ID's as well as its type ..
hopefully this makes sense, Im going crazy trying to figure out how to implement this..

Andrew
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There are really only three relation types: one-to-one, one-to-many and many-to-many. Only many-to-many requires a third table.
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Post by arpowers »

All my relationships here are many to many... think social networking..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Separate tables for each relation is the easiest to maintain, despite looking unwieldy. Unless you are prepared to live with the added logic you would need to use for the combined variant, I would go with separate tables.

Provided you have the right abstraction level in classes little of your application level code would need changing, if any, to do either.
Post Reply