Relational Database Design
If your model is fixed as that which you state in the example data, and you enforce it as the general parent/child relationship will allow data. It depends on how you want to use your data and wether you want to favor queries over update and insert. Your solution is called the adjacent. The dimension schema of a parent-child hierarchy depends on a self-referencing relationship present on the dimension main table. main table in the AdventureWorksDW sample database.
Dimension Schema The dimension schema of a parent-child hierarchy depends on a self-referencing relationship present on the dimension main table. For example, the following diagram illustrates the DimOrganization dimension main table in the AdventureWorksDW sample database.
In this dimension table, the ParentOrganizationKey column has a foreign key relationship with the OrganizationKey primary key column. In other words, each record in this table can be related through a parent-child relationship with another record in the table. This kind of self-join is generally used to represent organization entity data, such as the management structure of employees in a department. Hierarchies and Levels Dimensions that do not have a parent-child relationship construct hierarchies by grouping and ordering attributes.
These dimensions derive the level names for their hierarchies from the attribute names. However, parent-child dimensions construct parent-child hierarchies by examining the data that the dimension main table contains, and then evaluating the parent-child relationships between the records in the table. For more information about parent-child hierarchies, see User Hierarchies. Parent-child hierarchies do not derive the names for the levels in a parent-child hierarchy from the attributes that are used to create the hierarchy.
A Quick-Start Tutorial on Relational Database Design
Instead, these dimensions create level names automatically by using a naming template-a string expression you can specify at the level of the parent attribute that controls how the attribute generates the attribute hierarchy. The power of relational database lies in the relationship that can be defined between tables. The most crucial aspect in designing a relational database is to identify the relationships among tables.
The types of relationship include: In a "company" database, a manager manages zero or more employees, while an employee is managed by one and only one manager. In a "product sales" database, a customer may place many orders; while an order is placed by one particular customer. This kind of relationship is known as one-to-many. One-to-many relationship cannot be represented in a single table.
For example, in a "class roster" database, we may begin with a table called Teachers, which stores information about teachers such as name, office, phone and email. To store the classes taught by each teacher, we could create columns class1, class2, class3, but faces a problem immediately on how many columns to create.
On the other hand, if we begin with a table called Classes, which stores information about a class courseCode, dayOfWeek, timeStart and timeEnd ; we could create additional columns to store information about the one teacher such as name, office, phone and email. However, since a teacher may teach many classes, its data would be duplicated in many rows in table Classes. To support a one-to-many relationship, we need to design two tables: We can then create the one-to-many relationship by storing the primary key of the table Teacher i.
The column teacherID in the child table Classes is known as the foreign key. A foreign key of a child table is a primary key of a parent table, used to reference the parent table. Take note that for every value in the parent table, there could be zero, one, or more rows in the child table.
- Your Answer
- Schema examples
- Recent Comments
For every value in the child table, there is one and only one row in the parent table. Many-to-Many In a "product sales" database, a customer's order may contain one or more products; and a product can appear in many orders. In a "bookstore" database, a book is written by one or more authors; while an author may write zero or more books.
This kind of relationship is known as many-to-many. Let's illustrate with a "product sales" database.
We begin with two tables: The table products contains information about the products such as name, description and quantityInStock with productID as its primary key. The table orders contains customer's orders customerID, dateOrdered, dateRequired and status.
Again, we cannot store the items ordered inside the Orders table, as we do not know how many columns to reserve for the items. We also cannot store the order information in the Products table.
To support many-to-many relationship, we need to create a third table known as a junction tablesay OrderDetails or OrderLineswhere each row represents an item of a particular order. For the OrderDetails table, the primary key consists of two columns: The many-to-many relationship is, in fact, implemented as two one-to-many relationships, with the introduction of the junction table. An order has many items in OrderDetails.
An OrderDetails item belongs to one particular order. A product may appears in many OrderDetails. Each OrderDetails item specified one product. One-to-One In a "product sales" database, a product may have optional supplementary information such as image, moreDescription and comment. Keeping them inside the Products table results in many empty spaces in those records without these optional data.
Furthermore, these large data may degrade the performance of the database. A record will only be created for those products with optional data. The two tables, Products and ProductDetails, exhibit a one-to-one relationship.
That is, for every row in the parent table, there is at most one row possibly zero in the child table. The same column productID should be used as the primary key for both tables.
Some databases limit the number of columns that can be created inside a table. You could use a one-to-one relationship to split the data into two tables. One-to-one relationship is also useful for storing certain sensitive data in a secure table, while the non-sensitive ones in the main table. Column Data Types You need to choose an appropriate data type for each column. Commonly data types include: Normalization Apply the so-called normalization rules to check whether your database is structurally correct and optimal.
First Normal Form 1NF: A table is 1NF if every cell contains a single value, not a list of values.
mysql - SQL Database Design, recursive parent-child relationship? - Stack Overflow
This properties is known as atomic. Instead, you should create another table using one-to-many relationship. Second Normal Form 2NF: A table is 2NF, if it is 1NF and every non-key column is fully dependent on the primary key. Furthermore, if the primary key is made up of several columns, every non-key column shall depend on the entire set and not part of it.
If unitPrice is dependent only on productID, it shall not be kept in the OrderDetails table but in the Products table. On the other hand, if the unitPrice is dependent on the product as well as the particular order, then it shall be kept in the OrderDetails table. Third Normal Form 3NF: A table is 3NF, if it is 2NF and the non-key columns are independent of each others.
In other words, the non-key columns are dependent on primary key, only on the primary key and nothing else. For example, suppose that we have a Products table with columns productID primary keyname and unitPrice. The column discountRate shall not belong to Products table if it is also dependent on the unitPrice, which is not part of the primary key. At times, you may decide to break some of the normalization rules, for performance reason e.
Make sure that you fully aware of it, develop programming logic to handle it, and properly document the decision. Integrity Rules You should also apply the integrity rules to check the integrity of your design: The primary key cannot contain NULL.