CODEWITHSUNDEEP
mysqlsqldatabase
BARON'S
BARON'S

Reputation: 133

Database schema for different object types

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 ?

  1. Single table
  2. 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.

Upvotes: 0

Views: 244

Answers (1)

John Woo
John Woo

Reputation: 263723

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...

Upvotes: 1

Related Questions