Database Reference
In-Depth Information
This approach is more flexible than concrete-table inheritance: it allows single queries to span
different product types, but at the expense of space. It also continues to suffer from a lack of
flexibility in that adding new types of product requires a potentially expensive
ALTER TABLE
operation.
Multiple-table inheritance
Another approach that's been used in relational modeling is multiple-table inheritance where
you represent common attributes in a generic “product” table, with some variations in indi-
vidual category product tables. Consider the following SQL statement:
CREATE
CREATE TABLE
TABLE
`
product
`
(
`
sku
`
char
(
8
)
NOT
NULL
,
`
title
`
varchar
(
255
)
DEFAULT
NOT NULL
NULL
,
`
description
`
varchar
(
255
)
DEFAULT
DEFAULT NULL
DEFAULT NULL
NULL
,
`
price
`
, ...
PRIMARY
PRIMARY KEY
KEY
(
`
sku
`
))
CREATE
CREATE TABLE
TABLE
`
product_audio_album
`
(
`
sku
`
char
(
8
)
NOT
NOT NULL
NULL
,
...
`
artist
`
varchar
(
255
)
DEFAULT
DEFAULT NULL
NULL
,
`
genre_0
`
varchar
(
255
)
DEFAULT
DEFAULT NULL
NULL
,
`
genre_1
`
varchar
(
255
)
DEFAULT
DEFAULT NULL
NULL
,
...,
PRIMARY
PRIMARY KEY
KEY
(
`
sku
`
),
FOREIGN
FOREIGN KEY
KEY
(
`
sku
`
)
REFERENCES
REFERENCES
`
product
`
(
`
sku
`
))
...
CREATE
CREATE TABLE
TABLE
`
product_film
`
(
`
sku
`
char
(
8
)
NOT
NOT NULL
NULL
,
...
`
title
`
varchar
(
255
)
DEFAULT
DEFAULT NULL
NULL
,
`
rating
`
char
(
8
)
DEFAULT
DEFAULT NULL
NULL
,
...,
PRIMARY
PRIMARY KEY
KEY
(
`
sku
`
),
FOREIGN
FOREIGN KEY
KEY
(
`
sku
`
)
REFERENCES
REFERENCES
`
product
`
(
`
sku
`
))
...
Multiple-table inheritance is more space-efficient than single-table inheritance and somewhat
more flexible than concrete-table inheritance. However, this model does require an expensive
JOIN
operation to obtain all attributes relevant to a product.
