I attached the DB schema, so it will be simple.

Problem: Need to get the item records from the company e.g. 'A'.

Should I add to the item table company_id field? Will it be a redundancy info as we can always take it using user_company ?

Thank you in advance.enter image description here


In my personal preference, I would advice you not to add company_id in item table, as this will reduce the purpose of user_company table and both the Id will be repeated in Item table, of no use.

For retrieving item records from the company e.g. 'A'. Simple join can be used.

| improve this answer | |

No, you should not add it as this will create redundant data and unneeded foreign keys. As Vijayp mentioned, this would make the user_company table less functional in its current setup. It would be poor schema design, aside from any preference, to implement what you have suggested.

The following example would be a way to get the data (used for simplicity, not the best way to write the query necessarily):

SELECT * FROM item i, user_company uc, company c WHERE i.creator_user_company_id = uc.id AND uc.id = c.id

| improve this answer | |
  • Don't use of *, Add column name you want to fetch in select query. – vijayp Sep 10 '14 at 13:37
  • @vijayp As I mentioned, the example was simple for clarity's sake since the question did not list any other actual fields aside from the ID columns. – LowlyDBA Sep 10 '14 at 13:50
  • agree with you. – vijayp Sep 10 '14 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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