Object storage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Object storage

Author

Topic

jose_piratilla
Starting Member

7 Posts

Posted - 2008-10-13 : 17:06:00

Hi again.

I would like to comment you about a topic we are discussing. We have a database where one item is a product. But this product can have different characteristics, depending of the type. We have conceptually modeled it like an object. We ha a product object, with IdProduct, Name and some more standard characteristics. Some products are vehicles, with model, serial number, etc. Some vehicles are cars, and so on. How would you define it in a database?.

I thought in three options:
1.- To save all types of objects in the same table, with all possible fields of all object types.
2.- To create a table for each type of object. Each table containing all the fields of its object.
3.- To create a product table. Then create a vehicles table, and add only the fields in vehicles that are not listed in the product table and relate both tables in a one-to-one relationship. The create a cars table, relate to vehicles table and add only the fields that are not listed in the vehicles table.

What do you think?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-14 : 16:20:43

Only an approach:

1.)
product_area(
PK_product_area_id int,
product_area_name varchar(255)
)

2.)
product_sub_area(
PK_product_sub_area_id int,
FK__product_area_id int,
product_sub_area_name varchar(255)
)

3.)
product(
PK_product_id int,
FK_product_sub_area_id int,
product_name varchar(255),
product_brand varchar(255)
)

4.)
product_specification(
PK_product_specification_id int,
FK_product_id int,
FK_specification_value_id int
)

5.)
specification_value(
PK_specification_value_id int,
spec_type smallint,
spec_value varchar(255)
)

6.)
product_sub_specification(
PK_product_sub_specification_id int,
FK_product_specification_id int,
FK_specification_value_id int,
price_of_sale money,
cost money
)

Values for example:
1.)
vehicles

2.)
cars

3.)
name: crossfire
brand: chrysler

4. / 5.)
type: color
value:black

5. / 6.)
type: size
value: 3.0 ltr

second example:
1.)
clothes

2.)
outerwear

3.)
name: t-shirt
brand: g-star

4. / 5.)
type: color
value:blue

5. / 6.)
type: size
value: XXL

Greetings
Webfred

Planning replaces chance by mistake

jose_piratilla
Starting Member

7 Posts

Posted - 2008-10-14 : 18:28:12

Yes, this is an option that I didn't think. But I see two flaws:

1.- The application is continuous searching for the products data. I think that this kind of strategy will eat a lot of resources when query it.

2.- It only consider two levels of heritage. We need more levels. I know that this could be easily solved by using an auto-relationship like employee-manager in table 1.

But I also see a lot of advantages...

Me and my team will also consider this option. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources