Geek Answers Handbook

Elements and specialized elements: multiple tables with repeating columns, the main table and the detail table, or ...?

I need to store data related to "elements" where there will be different types of elements, all with common attributes, and then each type with its own additional attributes. I expect this to be a general requirement; What is the best practice solution? We are using SQL Server.

Use the above example:

Car has

  • Price
  • Make sure
  • Model
  • Owner

(There will be 10-15 common columns in our real data.)

A car is a car plus:

  • Style (sedan, sport, etc.).
  • Colour
  • Engineize

A boat is a car plus:

  • Bias
  • PortOfOrigin

... ... . 2-5 ; 5 . , , , 3 4 ( ). , , -. , , , , . .

( , ); (, ).

:

  • , .. .
  • Vehicle, Car Boat.
  • , . , .
  • , , DB.

:

  • , .. . , :

    CREATE TABLE [Cars] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Id] INT PRIMARY KEY,
    [Style] NVARCHAR(200),
    [Color] NVARCHAR(200),
    [EngineSize] DECIMAL(19, 2)
)
CREATE TABLE [Boats] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Id] INT PRIMARY KEY,
    [Displacement] DECIMAL(19, 4),
    [PortOfOrigin] NVARCHAR(200)
)

    , Cars Boats. , . , . ( Id).

  • Vehicle, Car Boat. , :

    CREATE TABLE [Vehicles] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Type] INT        -- A type ID, e.g. "Car" vs. "Boat"
)
CREATE TABLE [Cars] (
    [Id] INT PRIMARY KEY,
    [Style] NVARCHAR(200),
    [Color] NVARCHAR(200),
    [EngineSize] DECIMAL(19, 2)
)
CREATE TABLE [Boats] (
    [Id] INT PRIMARY KEY,
    [Displacement] DECIMAL(19, 4),
    [PortOfOrigin] NVARCHAR(200)
)

    , Vehicles Cars. Vehicles Boats. , . Vehicle. Car Boat .

  • , . , . , :

    CREATE TABLE [Vehicles] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Type] INT
)
CREATE TABLE [VehicleDetails] (
    [VehicleId] INT,
    [Name] NVARCHAR(200),
    [Value] NVARCHAR(MAX)
)

    , Vehicles VehicleDetails ( "", "" "EngineSize" ). Vehicle. . , , , .

  • , -DB:

    CREATE TABLE [Vehicles] (
    [Id] IDENTITY PRIMARY KEY,
    [Price] DECIMAL (19, 4),
    [Make] NVARCHAR(200),
    [Model] NVARCHAR(200),
    [Owner] INT,
    [Type] INT,
    [Detail01] NVARCHAR(MAX),
    [Detail02] NVARCHAR(MAX),
    [Detail03] NVARCHAR(MAX),
    [Detail04] NVARCHAR(MAX),
    [Detail05] NVARCHAR(MAX),
    [Detail06] NVARCHAR(MAX),
    [Detail07] NVARCHAR(MAX),
    [Detail08] NVARCHAR(MAX),
    [Detail09] NVARCHAR(MAX),
    [Detail10] NVARCHAR(MAX)
)

    , Car Style Detail01, Detail02 EngineSize Detail03; Displacement Detail01 PortOfOrigin Detail02. , , , .

+5
1

.

1 , .

2 , .

3 - 1, Entity-Attribute-Value . , , , - , .

4 - , 1.

- Entity-Attribute-Value ( , 3 4). - - , . , - , .

+6

More articles:


All Articles