CODEWITHSUNDEEP
databaserelational-databaseentity-attribute-value
user3825815
user3825815

Reputation:

Best way to extend information on a relational database

Let's say that we have to store information of different types of product in a database. However, these products have different specifications. For example:

We want to store each specification in a column of a table, and all the products (whatever the type) must have a different ID.

To comply with that, now I have one general table named Products (with an auto increment ID) and one subordinate table for each type of product (ProductsPhones, ProductsTV...) with the specifications and linked with the principal with a Foreign Key.

I find this solution inefficient since the table Products has only one column (the auto incremented ID).

I would like to know if there is a better approach to solve this problem using relational databases.

Upvotes: 1

Views: 355

Answers (3)

Forklift
Forklift

Reputation: 969

While this can't be done completely relationally, you can still normalize your tables some and make it a little easier to code around.

You can have these tables:

-- what are the products?
Products (Id, ProductTypeId, Name)

-- what kind of product is it?
ProductTypes (Id, Name)

-- what attributes can a product have?
Attributes (Id, Name, ValueType)

-- what are the attributes that come with a specific product type?
ProductTypeAttributes (Id, ProductTypeId, AttributeId)

-- what are the values of the attributes for each product?
ProductAttributes (ProductId, ProductTypeAttributeId, Value)

So for a Phone and TV:

ProductTypes (1, Phone) -- a phone type of product
ProductTypes (2, TV)     -- a tv type of product

Attributes (1, ScreenSize, integer) -- how big is the screen
Attributes (2, Has4G, boolean) -- does it get 4g?
Attributes (3, HasCoaxInput, boolean) -- does it have an input for coaxial cable?

ProductTypeAttributes (1, 1, 1) -- a phone has a screen size
ProductTypeAttributes (2, 1, 2) -- a phone can have 4g
-- a phone does not have coaxial input
ProductTypeAttributes (3, 2, 1) -- a tv has a screen size
ProductTypeAttributes (4, 2, 3) -- a tv can have coaxial input
-- a tv does not have 4g (simple example)

Products (1, 1, CoolPhone) -- product 1 is a phone called coolphone
Products (2, 1, AwesomePhone) -- prod 2 is a phone called awesomephone
Products (3, 2, CoolTV) -- prod 3 is a tv called cooltv
Products (4, 2, AwesomeTV) -- prod 4 is a tv called awesometv

ProductAttributes (1, 1, 6) -- coolphone has a 6 inch screen
ProductAttributes (1, 2, True) -- coolphone has 4g
ProductAttributes (2, 1, 4) -- awesomephone has a 4 inch screen
ProductAttributes (2, 2, False) -- awesomephone has NO 4g
ProductAttributes (3, 3, 70) -- cooltv has a 70 inch screen
ProductAttributes (3, 4, True) -- cooltv has coax input
ProductAttributes (4, 3, 19) -- awesometv has a 19 inch screen
ProductAttributes (4, 4, False) -- awesometv has NO coax input

The reason this is not fully relational is that you'll still need to evaluate the value type (bool, int, etc) of the attribute before you can use it in a meaningful way in your code.

Upvotes: 0

reaanb
reaanb

Reputation: 10065

The short answer is no. The relational model is a first-order logical model, meaning predicates can vary over entities but not over other predicates. That means dependent types and EAV models aren't supported.

EAV models are possible in SQL databases, but they don't qualify as relational since the domain of the value field in an EAV row depends on the value of the attribute field (and sometimes on the value of the entity field as well). Practically, EAV models tend to be inefficient to query and maintain.

PostgreSQL supports shared sequences which allows you to ensure unique auto-incremented IDs without a common supertype table. However, the supertype table may still be a good idea for FK constraints.

You may find some use for your Products table later to hold common attributes like Type, Serial number, Cost, Warranty duration, Number in stock, Warehouse, Supplier, etc...

Upvotes: 1

cham
cham

Reputation: 154

Having Products table is fine. You can put there all the columns common across all types like product name, description, cost, price just to name some. So it's not just auto increment ID. Having an internal ID of type int or long int as the primary key is recommended. You may also add another field "code" or whatever you want to call it for user-entered or user-friendly which is common with product management systems. Make sure you index it if used in searching or query criteria.

HTH

Upvotes: 0

Related Questions