Designing the Data Structure › Data Structure List › One-to-Many Relationships

One-to-Many Relationships

The following illustration shows a one-to-many (1:M) relationship:

This illustration is a data model fragment showing the following relationships:

• Each CUSTOMER entity sometimes places (relationship membership) one or more ORDER entities
• Each ORDER entity is placed by (relationship membership) exactly one CUSTOMER.

The identifier of CUSTOMER is an attribute called CUSTOMER NUMBER.

The following illustration is a Data Structure List showing the implementation of the customer number as a foreign key.

Type              Macro Name                    Format    Length  Optional
                                                                 
Table             CUSTOMER                                       
Column            NUMBER                 	Integer      4 	Not Null 
Column            NAME                   	Char        20 	Not Null 
Column            ADDRESS                	Char        78 	Not Null 
Index (U)                                                        
Column            PKEY (Primary)        	 	Integer      4 	Not Null 
                  NUMBER                                         
                                                                 
Table             ORDER                                          
Column            NUMBER                 	Integer      4 	Not Null 
Column            DATE_PLACED            	Date         8 	Not Null 
Column            TOTAL_VALUE            	Float      5,2 	Not Null 
FK Column         FK_CUSTOMER MNUMBER    	Integer      4 	Null     
RI Constraint     <No Name> Customer                             
Index             FKEY                                           
Column            FK_CUSTOMER MNUMBER    	Integer      4 	Null     
                                                                 
Index (U)         PKEY (Primary)         	Integer      4 	Not Null 
Column            NUMBER    

The ORDER table includes a Foreign Key (FK) column called FK_CUSTOMERNUMBER. For any ORDER, the number of the CUSTOMER that places it is stored in that column. This will allow your application user to access from the ORDER table the CUSTOMER who placed an ORDER or the ORDERS a CUSTOMER has placed.