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.

Improve this question
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
    Jul 31, 2015 at 8:15
  • Yeah thats correct @AndriyM - Should I be removing some foreign keys?
    – James111
    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
    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
    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?
    – ypercubeᵀᴹ
    Dec 20, 2017 at 10:05

1 Answer 1

Reset to default
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.

Improve this answer
2
  • You also said to put a switch the reference on the items>stocklist table?
    – James111
    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
    Jul 31, 2015 at 14:20

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