Database Reference
In-Depth Information
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
`
))
...
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
`
))
...
This approach has limited flexibility for two key reasons:
▪ You must create a new table for every new category of products.
▪ You must explicitly tailor all queries for the exact type of product.
Single-table inheritance
Another relational data model uses a single table for all product categories and adds new
columns any time you need to store data regarding a new type of product. Consider the fol-
lowing SQL statement:
CREATE
CREATE TABLE
TABLE
`
product
`
(
`
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
,
...
`
title
`
varchar
(
255
)
DEFAULT
DEFAULT NULL
NULL
,
`
rating
`
char
(
8
)
DEFAULT
DEFAULT NULL
NULL
,
...,
PRIMARY
PRIMARY KEY
KEY
(
`
sku
`
))
