SQL table design with common and distinct data

Admin 1
Viewed 65

I'm new to SQL and was wondering how to design tables when some data is common and some data is distinct. This is not a real example, but it illustrates the point. I want to store data about various items. All items have some common data - ID, Name, Description, CountryOfOrigin, UseByDate, etc. However, depending on the type of item, there are specific attributes that need to be stored, which are distinct for this type. It's a bit like object inheritance, there is a common root type, and various specialisations of the root type.

I could create different tables, one for each item type, but then each table will have its own Name, Description, CountryOfOrigin, UseByDate, etc. So if I wanted to list items by UseByDate, I would have to search multiple tables. This doesn't sound like a good approach.

So then I'm thinking, maybe I could have a single table called Items with common data and this somehow would reference other tables which store item type with its specific data. See the examples below:

CREATE TABLE Items
(
    ItemID integer PRIMARY KEY,
    Name text, -- Brand specific name or similar
    Description text,
    CountryOfOrigin text,
    UseByDate date
);

CREATE TABLE MilkVariety
(
    Type milk_type, -- FullFat, Skimmed, etc
    VolumeLitres integer,
    FatContent integer,
    PricePerLitre money
);

CREATE TABLE BreadVariety
(
    Type bread_type, -- White, WholeGrain, Baguette, etc
    WeightKilos integer,
    SugarContent integer,
    PricePerKilo money
);

But I'm not sure how Items table could reference MilkVariety or BreadVariety. I could introduce foreign keys, but then it looks like I'd have to have multiple such keys for each item type. And this doesn't sound right, as a single item cannot be both milk and bread type.

Any suggestions on how these things should be handled? Thanks.

database-design sql
2 Answers

I'm assuming, based on your comment, that a variety of food will have consistent attributes.

Your Item table looks fine. I've added a couple of columns at the bottom.

Item
----
Item ID
Name
Description
Origin Country
Use By Date
Item Variety
Variety ID

The Item Variety is a Varchar that describes the variety, as in "bread", "milk". The Variety ID is a foreign key to the Variety table.

 Variety
 -------
 Variety ID
 Type
 Measure Type
 Measure Amount
 Content Type
 Content Amount
 Price per measure

Since all the varieties are in one table, you can join the Item and Variety tables easily. However, you'll need to interpret what you've read from the database, probably in some programming language.

user_3002570

The way I understand it, in your example the whole heading of xVariety tables is unique and irreducible (KEY). Let us accept that, and also agree that we are happy tables are in 1NF and leave it at that.
Let us also agree that number of xVariety tables is manageable, say less than 30 -- not recommended for more.

Option 1

This is the preferred option.

Starting with new Variety, supertype of all other xVariety tables. VAR_ID may be an (auto-increment) integer generated in (for) this table; VAR_TYP is discriminator.
Create a new {VAR_ID, VAR_TYP} in this table before inserting a new row in any of xVariety tables,

-- Variety VAR_ID of type VAR_TYP exists.
--
Variety {VAR_ID, VAR_TYP}
     PK {VAR_ID}
     SK {VAR_ID, VAR_TYP}

CHECK (VAR_TYP IN ('Bread', 'Milk'))

xVariety subtypes are exclusive, so VAR_TYP is added to each subtype for better control, note FK {VAR_ID, VAR_TYP}.

MilkVariety {
        VAR_ID
      , VAR_TYP   DEFAULT 'Milk'

      , MilkType    -- FullFat, Skimmed, etc
      , VolumeLitres
      , FatContent
      , ...  -- Other columns specific to this variety
      }
   PK {VAR_ID}
   AK {MilkType, VolumeLitres, FatContent, ...}

           FK {VAR_ID, VAR_TYP} REFERENCES
      Variety {VAR_ID, VAR_TYP}

      CHECK (VAR_TYP = 'Milk')


BreadVariety {
        VAR_ID
      , VAR_TYP   DEFAULT 'Bread'

      , BreadType   -- White, WholeGrain, etc
      , WeightKilos
      , SugarContent
      , ...  -- Other columns specific to this variety
      }
   PK {VAR_ID}
   AK {BreadType, WeightKilos, SugarContent, ...}

           FK {VAR_ID, VAR_TYP} REFERENCES
      Variety {VAR_ID, VAR_TYP}

    CHECK (VAR_TYP = 'Bread')

Items can now reference Variety.

Items { ItemID
      , Name_
      , Description
      , CountryOfOrigin
      , UseByDate

      , VAR_ID
      }
   PK {ItemID}

   FK {VAR_ID} REFERENCES Variety {VAR_ID}

Option 2

This option provides less control, but may be easier to manage.

Each table has its own independent (auto-increment) ID; again adding VAR_TYP for better control.

MilkVariety {
         MILK_ID
       , VAR_TYP    DEFAULT 'Milk'

         MilkType      -- FullFat, Skimmed, etc
       , VolumeLitres
       , FatContent
       , ...  -- Other columns specific to this variety
       }
    PK {MILK_ID}
    AK {MilkType, VolumeLitres, FatContent, ...}

    CHECK (VAR_TYP = 'Milk')


BreadVariety {
         BREAD_ID
       , VAR_TYP    DEFAULT 'Bread'

         BreadType     -- White, WholeGrain, etc
       , WeightKilos
       , SugarContent
       , ...  -- Other columns specific to this variety
       }
    PK {BREAD_ID}
    AK {BreadType, WeightKilos, SugarContent, ...}

    CHECK (VAR_TYP = 'Bread')

Now a view {VAR_TYP, VAR_TYP_ID}.

-- view (logically)
--
Variety {VAR_TYP, VAR_TYP_ID}
     PK {VAR_TYP, VAR_TYP_ID}

An example :

CREATE VIEW Variety AS
SELECT VAR_TYP
     , MILK_ID AS VAR_TYP_ID
FROM MilkVariety
UNION
SELECT VAR_TYP
     , BREAD_ID AS VAR_TYP_ID
FROM BreadVariety ;

Note that the view must UNION all xVariety tables. It would be good if we could (somehow) materialize this view, then it could be possible FK to it. But, leaving this discussion for some other time.

Adding {VAR_TYP, VAR_TYP_ID} to Items

Items { ItemID
      , Name_
      , Description
      , CountryOfOrigin
      , UseByDate

      , VAR_TYP
      , VAR_TYP_ID
      }
   PK {ItemID}

     -- how to implement this?
     FK {VAR_TYP, VAR_TYP_ID} REFERENCES
Variety {VAR_TYP, VAR_TYP_ID}

The FK must be implemented somehow. If the view is materialized, depending on a RDBMS, it may be possible to specify the constraint in SQL; otherwise, the application must take care of it.

If it is the case that xVariety tables rarely change, say once per week, then view Variety {VAR_TYP, VAR_TYP_ID} may actually become a table. The same process that loads xVariety tables may then populate the table using the query. In that case it would be possible to have FK to it from Items.

Use {VAR_TYP, VAR_TYP_ID} when joining, to make sure not to join to a wrong table.

SELECT *
FROM Items        AS a
JOIN BreadVariety AS b ON  b.VAR_TYP_ID = a.VAR_TYP_ID
                       AND b.VAR_TYP    = a.VAR_TYP
WHERE a.CountryOfOrigin = 'US';

Notes

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
user_1967130
Related