MySQL Forums
Forum List  »  Newbie

Re: Foregin Keys
Posted by: Phillip Ward
Date: May 21, 2014 05:16AM

Quote

First: The topic foreign keys is where I must be looking for?
I think there's a few things we need to straighten out before we get to those.

Quote

The customer must be at least 12 digits long, the numbers are not consecutive, must be unique, the customer id is a code composed by different things thats why is very long number.
An Identifier should be just that - an identifier.
If you want to hold other pieces of information about a Customer then do so, but hold them in separate fields (or, possibly, even additional tables).
As soon as you start "bolting" data fields together in a table, you should have alarm bells ringing in your head.
Databases are really good at finding stuff and putting fields together for you - they're really, really rubbish at pulling fields apart.

Quote

Second: Is it correct if I put one product per table? something like this?
Absolutely not.
MySQL has a [perfectly sensible] hard limit wherein you can only join 60 tables together in a query. So, are you only ever going to have 60-odd products? I don't think so.

Quote

Third: If creating tables like these is correct, How I will relate the customer Id from tb0 to tb1, tb2 and so...
It's not and so you don't need to.

All of your products should be in one table. You might need "extra" bits of data depending on the Type of product; consider "classifying" these products and creating an extra table to hold data about each Type of product. For example, selling car tires, you'd need sizes and specifications that just don't apply to T-shirts and vice versa but all T-shirts need the same "extra" data, so you might consider creating extra tables for Product_T_shirts and Products_Tires.

Quote

Fourth: So I must do a 'REFERENCES' from tb1.customer to tb0.customer?
All of your Customers should be in one table.


So; what might all of this look like?

-- You have lots of Customers in a few Regions so extract Region into its own table.  
-- Change a region name in one record and it changes for everybody. 
select * from Regions ; 
+----+-------+
| ID | Name  | 
+----+-------+
|  1 | North | 
+----+-------+

-- Again, many Customers of a handful of Types. 
select * from Customer_Types ; 
+----+-----------------------+
| ID | Description           |
+----+-----------------------+
| 22 | Whatever This Type Is | 
+----+-----------------------+

-- People. 
select * from Customers ; 
+-----+-----------+---------+---------+-----------+
| ID  | Region_ID | Type_ID | Surname | Forenames |
+-----+-----------+---------+---------+-----------+
| 333 |         1 |      22 | White   | Jack      | 
+-----+-----------+---------+---------+-----------+

-- Products - with no mention of who bought them, because you can have something you never sold. 
select * from Products ; 
+------+-------+--------+
| ID   | Style | Colour | 
+------+-------+--------+
| 4444 | polo  | bule   | 
+------+-------+--------+

select * from Transactions ; 
+-------------+------------+---------------------+-------+
| Customer_ID | Product_ID | Purchase_Date       | Value | 
+-------------+------------+---------------------+-------+
|         333 |       4444 | 2014-05-20 18:01:02 |  1.99 | 
+-------------+------------+---------------------+-------+

And for your Foreign Keys (I haven't forgotten!)

Customers.Region_ID -> Region_ID to Regions.ID
Customers.Type_ID -> Customer_Types.ID
Transactions.Customer_ID -> Customers.ID
Transactions.Product_ID -> Products.ID

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
May 20, 2014 10:32AM
Re: Foregin Keys
May 21, 2014 05:16AM
May 21, 2014 08:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.