Database and SQL

 1-- Create the Base Relationship, has a unique primary key amongst both tables.
 2CREATE TABLE [dbo].[Person](
 3    [PersonId] [int] NOT NULL,
 4    [Name] [varchar](50) NOT NULL,
 5CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
 6(
 7    [PersonId] ASC
 8) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 9) ON [PRIMARY]
10
11
12--Create the other side of the 1:1 relationship   
13CREATE TABLE [dbo].[Contact](
14    [ContactPersonId] [int] NOT NULL,
15    [ContactNumber] [varchar](30) ,
16CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
17(
18    [ContactPersonId] ASC
19) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
20) ON [PRIMARY]
21
22
23-- Example of 1:1 relationship
24-- The contact primary key is the is also a foreign key relationship to the PersonId.  
25-- Therefore creating a 1:1 relationship.
26 
27ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Person]     
28FOREIGN KEY([ContactPersonId])
29REFERENCES [dbo].[Person] ([PersonId])
30GO 

 1/**//// <summary>
 2///    Holds a Contact object
 3///    which is related to this object through the relation Contact
 4/// </summary>
 5[BindableAttribute()]
 6public Contact Contact
 7...{
 8    get ...{ return entityData.Contact; }
 9    set ...{ entityData.Contact = value; }    
10}

 1private Person _contactPersonIdSource = null;
 2
 3/**//// <summary>
 4/// Gets or sets the source <see cref="Person"/>.
 5/// </summary>
 6/// <value>The source Person for ContactPersonId.</value>
 7[Browsable(false), BindableAttribute()]
 8public virtual Person ContactPersonIdSource
 9...{
10    get ...{ return this._contactPersonIdSource; }
11    set ...{ this._contactPersonIdSource = value; }
12}

 1    -- ORDER
 2    -- A familiar pattern in database design, which is the Header/Details pattern in which 
 3    -- one table contains the the overall summary of all the 
 4    -- detail items contained within a particular detail Table.
 5    -- Think of a single order always *could* potentially have many order items.  
 6    -- Just like on your grocery receipt. 
 7    
 8    -- the Header Table
 9    CREATE TABLE [dbo].[Orders](
10        [OrderID] [int] IDENTITY(1,1) NOT NULL,
11        [CustomerID] int NOT NULL,
12        [EmployeeID] [int] NULL,
13     CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
14    (
15        [OrderID] ASC
16    ) ON [PRIMARY]
17    ) ON [PRIMARY]
18
19
20    -- the Details Table
21    CREATE TABLE [dbo].[Order Details](
22        [OrderID] [int] NOT NULL,
23        [ProductID] [int] NOT NULL,
24     CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED 
25    (
26        [OrderID] ASC,
27        [ProductID] ASC
28    ) ON [PRIMARY]
29    ) ON [PRIMARY]
30
31    -- the Customer Table
32    CREATE TABLE [dbo].[Customer](
33        [CustomerID] [int] NOT NULL,
34        [ContactName] [nvarchar](30) NULL,
35     CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
36    (
37        [CustomerID] ASC
38    ) ON [PRIMARY]
39    ) ON [PRIMARY]
40    
41
42    Example of Many To One relationship:
43    -- This creates a simple relationship between the Orders Table and the Customer, so that in your 
44    -- for every OrderEntity entity, you will have a CustomerId foreign key integer, and a 
45    -- CustomerIdSource entity of type Customer nested composite entity in your Order class.    
46
47ALTER TABLE [dbo].[Order]  WITH NOCHECK ADD  CONSTRAINT [FK_Order_Customer] 
48    FOREIGN KEY([CustomerID])
49    REFERENCES [dbo].[Customer] ([CustomerID])
50    GO 
51
52    Example of One to Many Relationship:
53    -- This creates a one to many relationship between the Order and the Order Detail tables.  
54    -- Meaning, in your generated code, 
55    -- for every Order entity, you will have an OrderDetailCollection of type TList<OrderDetail>.  
56    -- This means you can have an entire
57    -- list of OrderDetail entities within your Order entity.
58    
59ALTER TABLE [dbo].[Order Details]  WITH NOCHECK ADD  CONSTRAINT [FK_Order_Details_Orders]             FOREIGN KEY([OrderID])
60    REFERENCES [dbo].[Orders] ([OrderID])
61    GO