How to create a relationship?

How to create a relationship?

• SP534

  Hi,

  The basic requirements for establishing relationship(s) among tables is to set a logical DB design and then identify the objects:

  • Tables and their names (ENTITIES)
  • Column names (ATTRIBUTES) for each table.

  In your example we have the Entities as :Person,Employee ; Attributes for Person:Name;Attributes for Employee:Person Name

  • Identify Column Charecteristics: requiring unique values and allowing nulls,and the type of data the column will store Ex:Varchar(30) for Name and PersonName.
  • The Primary Key for each table:

    This is the column (or set of columns) that stores values that uniquely identify each row in a table. Although other unique columns may exist in the table, only one is identified as the unique access key for retrieving rows—the primary key. Each table can have only one primary key. Although a primary key is not strictly required, you should always define one.

  Relationships between tables. Rows in some tables are dependent on one or more rows in another table. These intertable dependencies are called relationships. To define a relationship, a column (or set of columns) in one table, called a foreign key, references the primary key of another table.

  For example, each row in an Orders table is dependent on a row in a Customers table because each order must be placed by a customer. This is a relationship between the Orders table and the Customers table. The Orders table must have a column that holds values that reference an individual row in the Customers table. Rows in the Orders table must be guaranteed to reference only one customer so the relationship should be based on the primary key of the Customers table. The Orders table column that references the primary key of the Customers table is called a foreign key.

  Types of Relationship

  Three types of relationships are possible between tables:

  • One-to-one. Each row in the primary is related to only one row in the foreign table. A one-to-one relationship is implemented by defining the foreign key to be unique (that is, not allow duplicates).
  • One-to-many. Each row in the primary table is related to one or more rows in the foreign table. For example, one customer can place many orders, but one order cannot be placed by many customers.
  • Many-to-many. Many rows in one table are related to many rows in another table. For example, an author can write many books and a book can be written by more than one author. A many-to-many relationship between two tables is implemented by creating a third table and creating a one-to-many relationship to this junction table from each of the initial tables.

  After identifying the realationships it is also necessary to redundant data and inconsistent dependencies. For example,

  Name and PersonName may have common names. So, the changes made to 'Name' at one location should apply to all the locations wherein it is stored.

  To avoid this, have the attribute stored in one table and reference it through different keys that relate to it.

  Ex: Customer Address is the attribute and we need it in tables like :invoices,shipping address,billing address,Orders and customers tables.

  Either define it in a separate table(needs to be created) called "Address" that has a primary key and relate to that key to fetch the address or define it in one existing table like customers and refer to its key to fetch the address each time. The first alternative is always beneficial. But, the second alternative can be adopted if you have memory problems.

  
  
  

• UnKnown Nick

  open the database diagram

  add the tables that you need

  drag the field from the parent table to the child table.

  this will create a relationship

  requirements. the field from the parent table

  should either have unique index or is a primary key

  
  
  

• johngccfc

  A question for you, are you going to be enforcing this relationship via a constraint

  Scenario 1 - You are going to be enforcing this relationship.

  SQL Server requires that both columns in a referential constraint have the same data type and length (size). They are not required to have the same name. If you are going to do this my recommendation is to use a SQL Server Management Studio 2005 to create the relationship while using diagram view, it will be easiser for a person who is just starting with SQL Server.

  Scenario 2 - You are not going to be enforcing this relationship, you just need to query data ...

  In this case you could just write a fancy T-SQL Select statement to get the data from the Database regardles of DataType, Length (size), or Field Names . The SELECT statement you would have to write would use a JOIN statement to specify that you are going to be querying the database for the information on two tables, and you would specify the fields (condition) to join the information by on the ON statement.
  
  Ex.
  
  SELECT *
  FROM table1
  JOIN table2
  ON table1.field1 = table2.field2
  
  Hope this helps,
  
  Roberto Hernandez-Pou
  http://www.rhpconsulting.net

  
  
  
• How to create a relationship?