Logical database Design-variable properties (inheritance)

Variable attribute requirements: We need to store a lot of appliances in the database, such as TV, refrigerator and so on. Typically, in a program, our class diagram is:

Eva Design

One way to do this kind of inherited mutable property is to create another table that stores properties as rows.

　　

The data stored here looks like this:

　　

Such a design is called: The entity-genus-value, abbreviation: EVA, or also called open architecture, no mode.

There are 3 benefits of this design:

1. These two tables have very few columns.

2. The new attribute does not affect the existing table structure, and no new columns are required.

3, avoid the empty value caused by the table content confusion.

But this also has the following disadvantages:

1. Query Properties

Originally, we want to follow the factory date query, only need:

SELECT  from Electric

But this is not the way it needs to be:

SELECT  as ' dateofmanufacture '  from Attribute WHERE = ' dateofmanufacture '　　

2. Cannot declare mandatory properties

Originally, we want to ensure that the Dateofmanufacture (factory date) This property has a value, in the traditional database design, it is necessary to simply declare a NOT NULL is OK.

But now in the EVA design, each attribute corresponds to a row in the attribute. We need to establish a constraint to check that there is a row for each electricid, and that the attrname of this line is dateofmanufacture. And the attrvalue of this line of records are not empty and conform to the date format.

3. Unable to use SQL data type

Because the Attrvalue format can only be declared as a varchar or nvarchar type, the date format that the user enters may be various, and even some are not date formats at all.

Because the data type cannot be restricted, we do not get an error if we execute the following SQL statement.

INSERT VALUES　　(1,'dateofmanufacture',' I'm not a date ') --Such a statement does not error

4. Referential integrity cannot be ensured

To join the above design, we need to add a branding attribute. The optional values must be present such as Samsung, Konka, Haier and so on. In the traditional database design, we only need to design a brand table, and add a brand ID field to this table, the establishment of FOREIGN key constraints can be.

However, in the EVA Design, because the brand attribute corresponds to a line, we cannot use foreign keys to ensure referential integrity. If we do not process, then the value of the brand attribute entered by the user may not exist.

5. Repeat record

In the EVA design, we may have the same attribute two times.

Because, we continuously execute the following SQL statement twice also do not error:

INSERT VALUES (1,'dateofmanufacture','2013-09-09') INSERT VALUES (1,'dateofmanufacture','2013-09-10' )

Since there may be duplicate records, we do not count the number of manufactured products by factory date. At the same time, by date statistics, is also very complex.

SELECT COUNT (*as  per_date   from (SELECTDISTINCT as elcdate          from Attribute         WHERE = ' dateofmanufacture ' )  GROUP by elcdate

This is the notation in Oracle.

6. Reorganization Column

In the traditional database design, join us to display a complete record, we only need to:

SELECT *  from Electric

But now, we want to:

SELECTI.electricid, I1. AttrValue as 'Name', I2. AttrValue as 'dateofmanufacture', i3. AttrValue as ' Screen' 　　  fromElectric asI Left OUTER JOINAttribute asI1 onI.electricid=I1. Electricid andI1. Attrname='Name' 　　　　  Left OUTER JOINAttribute asI2 onI.electricid=I2. Electricid andI2. Attrname='dateofmanufacture' 　　　　  Left OUTER JOINAttribute asi3 onI.electricid=i3. Electricid andi3. Attrname=' Screen'

Not much to say, in short, the above design, not a very resistant to the design of the scrutiny.

Solution Solutions

　　One, single-table inheritance

Single-table inheritance is designed to have all related types present in one table and one column for all properties of all types. Also use a property to define the subtype represented by each row.

For example, for the above electrical requirements, the single-table inherited data is designed as follows:

　　

The way that single-table inheritance can be understood is that all the sub-class fields are placed in the table, stored, when a child entity does not have, the corresponding class is empty, is reserved a column as the tag type.

The disadvantage of single-table inheritance is that:

• Too many columns.
• Too many null values.
• When you want to add attributes, change the table structure.

　　In summary: Single-table inheritance is only a case where there are few special attribute columns that are suitable for using subclasses.

　　Second, Entity table inheritance

Entity table inheritance can be understood as: When a child table is designed, all the properties of the parent table are defined more than once in this table.

Back to the example above, if you inherit from the entity table, the corresponding design is as follows:

　　

An advantage of Entity table inheritance compared to single-table inheritance is that it prevents storing too many properties that are not related to the current subtype in a row. For example, there is no screen column in the refrigerator table, whereas in single table inheritance, it is the null value of the scree column. In addition, it is not necessary to add more than one column to mark what is currently in the appliance.

Fatal disadvantage of entity table inheritance:

Too many repeating columns

Repeating the column too much, it is easy to let people feel the mind.

　　Third, class table inheritance

My recommendation, my favorite, I think the most reliable way

Class table inheritance simulates inheritance in high-level programming languages and treats tables as classes in object-oriented. Creates a base table that contains the public properties of all subtypes. For each subtype, create a separate table that is connected by the foreign key and the base class table.

For the above example, Class table inheritance is designed as follows:

　　

Class table inheritance, as opposed to entity class inheritance, is obviously a bit less than a lot of repeating columns. In a subclass table, the primary key is also a foreign key.

I think this is a better approach.

　　Iv. Semi-structured

Semi-structured, actually similar to single-table inheritance. Single-table inheritance is multiple columns, while semi-structured uses a new feature, such as a column of an XML type, to store the properties of a subclass.

For the above example, the semi-structured design is as follows:

　　

Subclass of information, there is an XML column in which you love to set what node on what node. It is not troublesome to inquire anyway. It is not enough to remember that there is a type column to mark which line is which type of appliance. Otherwise it's all messed up.

Because SQL Server supports XML more and more now, this is a good choice.

Logical database Design-variable properties (inheritance)