0

I would imagine that this will be a simple answer for someone who has developed many database schemas, but I have recently found myself tasked with optimizing (or attempting to optimize rather) a database schema and have been reading through "High Performance MySQL", and am left with a question concerning the use or "over-use" of foreign key relationships within a schema. For example lets say I have the following tables:

CUSTOMERS: __________________________________ |CustIDPK| Name | RegDate | ---------------------------------- | 1 | frank | 01-30-2014 | | 2 | terry | 02-01-2014 | | 3 | amber | 02-02-2014 | | 4 | sara | 02-06-2014 | PRODUCTS: ____________________________________ | ProdIDPK | ProdName | AddedDate | ------------------------------------ | 1 | Phone | 01-01-2014 | | 2 | TV | 01-02-2014 | | 3 | Tablet | 01-02-2014 | | 4 | PC | 01-05-2014 | PRODUCT_RATINGS: __________________________________________________________________ | ProdRateIDPK | ProdIDFK | CustID | Rating | TimeRated | ------------------------------------------------------------------ | 1 | 1 | 1 | 8 | 01-01-2014 | | 2 | 1 | 2 | 7 | 01-01-2014 | | 3 | 1 | 3 | 8 | 01-02-2014 | | 4 | 2 | 4 | 6 | 01-02-2014 | | 5 | 2 | 4 | 6 | 01-03-2014 | | 6 | 2 | 3 | 4 | 01-01-2014 | | 7 | 3 | 2 | 5 | 01-02-2014 | | 8 | 3 | 1 | 7 | 01-03-2014 | | 9 | 3 | 1 | 4 | 01-04-2014 | | 10 | 4 | 2 | 9 | 01-04-2014 | | 11 | 4 | 3 | 8 | 01-01-2014 | | 12 | 4 | 4 | 7 | 01-01-2014 |

The CUSTOMERS table exists independently of the PRODUCTS table therefore no relationship is defined. The PRODUCTS table has a one-to-many relationship with the PRODUCT_RATINGS table since any one product can have many ratings. This much is clear.

Now in the existing schema within the PRODUCT_RATINGS table the column CustID is a foreign key to the CUSTOMERS table, representing a one-to-many relationship with the product ratings since any one user can have many ratings in this table (each rating representing a separate product).

My question: Should the 'CustID' column be defined as a foreign key creating a one-to-many relationship with the CUSTOMERS table? I do not see where the need for a join of this data would be necessary. From what I can tell, the 'CustID' column is only used in the application to distinguish which customer issued the rating...

Improve this question
2
  • Note that ProdRateIDPK is rendundant in this schema - on the basis that you have a perfectly adequate PK on (prodid,cust) or (prodid,custid,timerated) Commented Feb 8, 2014 at 18:10
  • Any two tables can be joined. The presence of a FK between them just means that the join will have one result row for each referenced table row. Just declare all candidate keys (ie minimal PK/UNIQUE column sets, ie column sets that don't contain smaller ones) & FKs to them. (SQL requires you to declare PK/UNIQUE column sets that are referenced in FKs. So you must also declare those non-minimal PK/UNIQUE column sets and coresponding FKs, which are hence actually foreign superkeys.) Commented Sep 20, 2015 at 2:49

2 Answers 2

Reset to default
1

I am not familiar with this problem that you mention: '"over-use" of foreign key relationships within a schema'. Generally the problem is under-use.

Defining the foreign key relationship does several things. Most importantly, it guarantees that the CustId column in the PRODUCT_RATINGs table is a valid CustId in the CUSTOMERS table. That is very useful.

There are consequences to this, but clarifying that this relationship exists is a part of good schema design. It is not something to be removed by an unusual notion of "optimization".

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I think the thing that through me for a loop was when they started talking about normalizing and denormalizing for your specific needs and started to think that if a join wasn't happening that the CustID column wouldn't need to be indexed as a foreign key in the PRODUCT_RATINGs table. I understand what you mean about guaranteeing that the CustID is valid. Serious issues could arise if a customer is removed but there is unrelated data still in existence.
0

Yes the CustId field in the product_ratings table should be a foreign key to the customers table. That is what database normalization is all about. I didn't read the same book you did, but I have heard good things about Database Design for Mere Mortals.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.