Database schema for different object types

Question

I am creating a new application that have objects of different types. For example is monitor, mouse and keyboard. Different objects have its own properties.

Monitor: brand, resolution, color, etc Mouse: brand, type, wire/wireless Etc

Question: What is the best schema for different types of objects ?

Single table
Multiple table

Given single table (query performance reason), the properties would be db column type, but as object of different types are of different properties, how is this possible ? And there maybe unknown object at the design time that maybe added in the future. And would this result in a lot of columns ?

On other hand, would multiple table suffer from performance issue (querying multiple table) when listing all objects ?

Thanks in advance.

John Woo · Accepted Answer · 2013-03-22 12:39:58Z

1

This sounds somewhat like Many-to-Many relationship and in this case you should be creating 3-table design,

ObjectList

ObjectID (PK)
ObjectName
other columns...

PropertyList

PropertyID (PK)
PropertyName
OtherColumns

Object_Property

ObjectID (FK) -- this sould be compound primary key with PropertyID
PropertyID (FK)
otherCOlumns...

answered Mar 22, 2013 at 12:39

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database schema for different object types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related