Databases
StackOverflow2013
Postsdbo
PODatabase design advantages for using one-to-many vs many-to-many relationships
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase design advantages for using one-to-many vs many-to-many relationships
    primarykey
    data
    text
    <p>I'm trying to design a new database structure and was wondering of any advantages/disadvantages of using one-to-many vs many-to-many relationships between tables for the following example.</p> <p>Let's say we need to store information about clients, products, and their addresses (each client and product may have numerous different addresses (such as "shipping" and "billing")).</p> <p>One way of achieving this is: <img src="https://i.stack.imgur.com/MngIo.png" alt="One-to-many relationships"></p> <p>This is a rather straight forward approach where each relationship is of one-to-many type, but involves creating additional tables for each address type.</p> <p>On the other hand we can create something like this: <img src="https://i.stack.imgur.com/eGOy3.png" alt="Many-to-many relationships"></p> <p>This time we simply store an additional "type" field indicating what kind of address it is (Client-Billing (0), Client-Shipping (1), Product-Contact (2)) and "source_id" which is either (Clients.ID or Products.ID depending on the "type" field's value).</p> <p>This way "Addresses" table doesn't have a "direct" link to any other tables, but the structure seems to be a lot simpler.</p> <p>My question is if either of those approaches have any significant advantages or is it just a matter of preference? Which one would you choose? Are there any challenges I should be aware of in the future while extending the database? Are there any significant performance differences?</p> <p>Thank you.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USrexem
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COOf the two, I like the first one because it is more normalized. Challenges in the future include handling address changes.
      singulars
      1. PODatabase design advantages for using one-to-many vs many-to-many relationships
      1. USDan Bracuk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload