MySQL Forums
Forum List  »  Newbie

Re: One to many relationships
Posted by: Phillip Ward
Date: December 06, 2016 05:40AM

Every Entity that exists in its own right gets its own table - that's Company, Customer, Location, Copier & Accessory.

Some of you relationships, like Company_Customer, are "one-to-many" so you can get away with linking each entity to its "parent" using the parent's unique identifier.
Others, though, like Location_Copiers, can only exist once instances of the two, linked entities exist in their "master" tables. These need an intermediate, linking table (a.k.a. "weak entity").

That gets you something like this:

/* Each Company */ 
select * 
from Companies ; 

+----+-------------+ 
| Id | Name        | 
+----+-------------+ 
|  1 | Stone & Co. | 
+----+-------------+ 

/* Each Customer within each Company */ 
select * 
from Company_Customers ; 

+----+------------+-----------------+ 
| Id | Company_Id | Name            | 
+----+------------+-----------------+ 
| 22 |          1 | Fred Flintstone | 
+----+------------+-----------------+ 

/* Each Location for each Customer */ 
select * 
from Customer_Locations ;

+-----+-------------+---------+ 
| Id  | Customer_Id | Name    | 
+-----+-------------+---------+ 
| 333 |          22 | Bedrock | 
+-----+-------------+---------+ 

/* Reference table of all copiers you sell */ 
select * from Copiers_Master ; 

+------+----------+-------+ 
| Id   | Make     | Model | 
+------+----------+-------+ 
| 4444 | Chizelle | Mk 1  | 
+------+----------+-------+ 

/* Reference table of all copier accessories */ 
select * 
from Copier_Accessories_Master ; 

+-------+-----------+-----------------+ 
| Id    | Copier_Id | Name            | 
+-------+-----------+-----------------+ 
| 55555 |      4444 | Supersharp Edge | 
| 66666 |      4444 | Marble CoolGrip | 
+-------+-----------+-----------------+ 

/* Each Copier and where it's installed */ 
select * 
from Location_Copiers ; 

+--------+-------------+-----------+ 
| Id     | Location_Id | Copier_Id | 
+--------+-------------+-----------+ 
| 777777 |         333 |      4444 | 
+--------+-------------+-----------+ 

/* Accessories installed on each Copier */ 
select * 
from Copier_Accessories ; 

+---------+--------------------+--------------+ 
| Id      | Location_Copier_Id | Accessory_Id | 
+---------+--------------------+--------------+ 
| 8888888 |             777777 |        55555 | 
| 9999999 |             777777 |        66666 | 
+---------+--------------------+--------------+ 

select cm.Make 
,      cm.Model 
,      cam.Name 
from       
           Companies co 
inner join Company_Customers cu 
      on   co.id = cu.company_id 
inner join Customer_Locations l 
      on   cu.id = l.customer_id 
inner join Location_Copiers c 
      on   l.id = c.location_id 
inner join Copiers_Master cm 
      on   cm.id = c.copier_id 
inner join Copier_Accessories ia 
      on   cm.id = ia.copier_id 
inner join Copier_Accessories_Master cam 
      on   ia.id = cam.copier_id 
where 
      co.Name = 'Stone & Co.' 
order by 
         cm.Make 
,        cm.Model 
,        cam.Name ; 

+----------+-------+-----------------+ 
| Make     | Model | Name            | 
+----------+-------+-----------------+ 
| Chizelle | Mk 1  | Marble CoolGrip | 
| Chizelle | Mk 1  | Supersharp Edge | 
+----------+-------+-----------------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
December 05, 2016 11:03AM
December 05, 2016 11:48AM
Re: One to many relationships
December 06, 2016 05:40AM


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.