Maybe a newbie question about foreign keys, but I want to know the answer.
Let's say I have 2 tables:
products -------- product_id (int) name (unique) (varchar) description (text) vendor (varchar) (foreign key: vendors.name)
vendors -------- name (varchar)
I know that I should use a
vendor_id (int), but this is just an example to help me ask my question.
So: if I create vendor: Apple, and product: 1, iPhone 4, Description.., Apple then the varchar "Apple" will be stored both in products and vendors, or just in vendors (because of the foreign key)? Is this a wrong db design?
This is called "normalization" in the database. In your example, there are a couple things to consider:
- In order for
productsto have a foreign key to
vendorsneeds a key. Is
namethe primary key for
vendors? If so, then the foreign key would also be a
varchar. In that case, yes, the value "Apple" would be stored in both. (Note that this isn't a very good idea.)
- If you add a
vendor_idinteger column to the
vendorstable, and it is the primary key for that table, then you can add a
vendor_id(or any other name) column to the
productstable and make it a foreign key to the
vendorstable. In this case, only that integer would be stored in both tables. This is where the data becomes normalized. A small, simpler data type (integer) links the tables, which contain the actual data which describes the records.
Only that key value is stored in both tables. It's used as a reference to join the tables when selecting data. For example, in order to select a given product and its vendor, you'd do something like this:
SELECT products.name, products.description, vendors.name AS vendor
FROM products INNER JOIN vendors ON products.vendor_id = vendors.vendor_id
WHERE products.product_id = ?id
This would "join" the two tables into a single table (not really, just for the query) and select the record from it.
It will be stored in both. The foreign-key constraint requires that every value in
products.vendor appear somewhere in
(By the way, note that MySQL only enforces foreign-key constraints if the storage engine is InnoDB.)