CODEWITHSUNDEEP
sqldatabase-design
reBourne
reBourne

Reputation: 127

How to model Abstraction in SQL?

I have a list of "home appliances" and i want to model a database to save them in a table. There are different device types with different properties/columns. Example :

User "mike" has a "TV (size inch ,energy consumption)", "fridge(min. tempreture,height,width)".

A user has a list of "home appliances".

I don't know how i could model this in a nice way. In the end, i just want to query the db : which appliances ( and their properties ) does the user have ?

Upvotes: 0

Views: 1688

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

This is a classic problem in data modeling and database design. It goes by different names than the one you used, "abstraction". In ER modeling, it tends to be called "generalization/specialization". In object modeling, it tends to be called "class/subclass" modeling, or "inheritance".

When it comes to designing SQL tables to match up with these models, there are several techniques that might help you. In the early years, SQL had no general facilities to help with this situation, in spite of the fact that it occurs over and over again in the real world. Two techniques that come to mind are "single table inheritance" and "class table inheritance".

Single table inheritance stuffs all the data for the class and the subclasses into one table. It's good for the simplest situations. Your case looks a little more complicated than that, to me.

Class table inheritance provides one table for class data and a separate table for each subclass. The answer from Jorge Campos looks like this. In connection with class table, there is a technique called "shared primary key". The subclass tables don't have an id field of their own. Instead, the foreign key that references the class table is used as the primary key of the subclass table. This makes joins simple, easy, and fast. It also enforces the one-to-one nature of the subclass relationship.

A quick search on these buzzwords should give you lots of descriptive examples. I particularly like the way Fowler presents these concepts. Or you could start with the info tab on these three tags:

Upvotes: 4

Jorge Campos
Jorge Campos

Reputation: 23361

You could create a model like:

user (id, name)
   1, bob
   2, mark
   ... 
device (id, name) 
   1, TV
   2, Fridge
   ...
attributes (id, name)
   1, size inch
   2, energy consumption
   3, min temperature
   4, height
   ...
device_has_attribute (id_device, id_attribute, value)
   1, 1, 2.7inches (you decide the unit or it could be another table)
   1, 2, 220v
   2, 1, 6.7ft
   2, 3, 30F
   ...
home_appliance (id, name)
   1, Kitchen
   2, Living room
   ...
home_appliance_device (id_home_appliance, id_device )
   1, 1
   2, 1
   2, 2
   ...
user_home_appliance (id_user, id_home_appliance)
   1, 1
   1, 2
   2, 2
   ...

I recommend you to add the value unit as another table it would be

unit (id, name, acronym)
device_has_attribute (id_device, id_attribute, value, id_unit)

Upvotes: 1

Related Questions