Search code examples
mysqlentity-relationship

Cardinality in ER diagram

I've made a project that's essentially an online bookstore where one can buy books and place the order.

My database contains various tables like:

  • user
  • user_shipping_address
  • user_payment_mode
  • user_order
  • order_shipping_address
  • order_billing_address
  • order_payment_details

I tried to construct the EERD diagram for this but I am confused about one thing: A user_order can only have one shipping address. I've created a foreign key order_id in the order_shipping_address table that references the primary key order.id. I also have a shipping_address_id foreign key in the table order that references order_shipping_address.id.

When I try to generate the ER diagram, it gives me two different relationships. A 1:1 relationship between the order and the shipping address and a 1:M relationship from the shipping address to the order. I don't know how to structure the foreign key constraints because I feel the order table should contain the shipping_address_id and the shipping address should contain the order_id, right? This just made everything more confusing.

Please help me about this.

Here is my EERD : enter image description here

Solution

  • This happens because your current design means it's possible for multiple user_order rows to reference the same single shipping_address row.

    You need to change the design so that it's impossible for multiple user_order rows to reference the same single shipping_address row.

    There are at least two different possible solutions:

    1. Add a UNIQUE constraint on user_order.shipping_address_id
    2. Or: invert the relationship (this my preferred option as it eliminates unneeded surrogate keys):
      1. Remove the user_order.shipping_address_id column.
      2. Change shipping_address.id to shipping_address.order_id, so that it's a foreign-key of user_order.id
      3. Make shipping_address.order_id the new primary key of shipping_address.

    Note that both of these options are a denormalization as it prevents the sharing of shipping addresses between different orders (e.g. if the same customer makes the same repeat order a lot) though this can be intentional - so that if a user's future address changes it won't unintentionally retroactively update old order shipping records.

    A few other tips:

    • Consider using int rather than bigint for your identities - I doubt you'll have over 2 billion rows in each table.
    • Don't blindly use varchar(255) for all text columns - use it to enforce reasonable constraints on data length, for example state doesn't need to be longer than 2 characters if you're storing abbreviations, ditto zipcode which can be varchar(10) if you're using ZIP+4.
    • DO NOT STORE FULL CREDIT CARD NUMBERS IN YOUR DATABASE! (as seen in your payment table) - This is a violation of PCI Rules, a massive liability and probably illegal negligence in your jurisdiction. Your payment processor will provide you with a substitute opaque token value (or something similar) as a means of identifying charge-cards and applying future charges to stored payment details - the most you can reasonably store is the last 4 digits. Whether or not your encrypt the data is largely irrelevant.