Search code examples
sqlentity-relationshipdatabase-normalization

Entity relation (normal forms)

Let's assume we have table USERS like:

..ID..username

  1. user1
  2. user2
  3. user3

Users can have Bills (user -> have_many -> bill relation).

Table BILLS like:

..ID..user_id

  1. 1
  2. 2
  3. 2

Also we have Products so every Product can be associated to ONLY ONE bill (product -> has_one -> bill relation).

Table PRODUCTS like:

..ID..bill_id

  1. 2
  2. 3
  3. 1

So, as you can see, our user can have lot of products (through bills).

My question:

Would it be correct DUE TO Database normalization to add second foreign key to PRODUCTS table named user_id to quickly select all user's Products from PRODUCTS table, or it's not correct and I should use JOIN statement to select all User's Products?

P.S. Sorry for dirty tables drawing )

Solution

  • I would rather go with the normalized view (where you DO NOT have a user_id in the products table).

    The only time I would ever consider this, as a last option, is if the performance REALY requires it.