3

I'm wondering if my schema is correct? It's intended to hold shopping list items for each user, a user belongs to a household. Items will be added to the household.

The stocklist will to hold the quantity of an iID (item ID) and that iID is belongs to a hhID .

So at the moment

  • stocklist.hhID references household.hhID
  • household.adminID references users.uID
  • items.hhID references household.hhID
  • stocklist.iID references items.iID

I'm wondering if these relationships are viable? Even more importantly what do you think of the schema?

Database EER diagram

Posted from my unused account before.

7
  • From your illustration it seems that users.hhID also references household.hhID. Is that so or is the picture just not up-to-date?
    – Andriy M
    Commented Jul 31, 2015 at 8:15
  • Yeah thats correct @AndriyM - Should I be removing some foreign keys?
    – James111
    Commented Jul 31, 2015 at 10:15
  • 1
    I would kill the relationship between Stocklist and Users and add a new tables Orders and OrderItems. Relate Household with Orders and Orders with OrderItems
    – Nelson
    Commented Jul 31, 2015 at 13:17
  • Hm,is this tool using lines instead of fields? In that case it seems User.hhid is not needed as it is given by the reference. If not, I am not sure which field is which reference?
    – eckes
    Commented Apr 26, 2017 at 2:41
  • It is not clear what relationships you want to implement. What is a shopping list? Is it the same as the stocklist? Can a user have many shopping/stock lists? Can a shopping/stock list have many items? Commented Dec 20, 2017 at 10:05

1 Answer 1

1

Your schema has a circular reference and redundant columns/foreign keys.

Consider the following normalization of your data structure:

  • Put itens as a reference table, no Foreign Keys.

  • Stocklist references Items

  • Users have a FK to Household, no use on including the reverse relation because you can always find the User records for a given Household with a table Inner Join.

2
  • You also said to put a switch the reference on the items>stocklist table?
    – James111
    Commented Jul 31, 2015 at 10:29
  • Yes for the first and no for the second. Consider removing Household references items. The stocklist referencing items is the relation you already have on your diagram.
    – Ozzie
    Commented Jul 31, 2015 at 14:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.