Should related objects be grouped in one table, or multiple?

Question

This is mostly a database related question, but I'm using VB.net and sqlite.

So I have a group of Widgets, they all have a certain group of properties. Among them is a "Type" property.

Depending on the Type property there are a series of additional type-dependent properties. I'm wondering if it's normally a good idea to group these into a single table (and then likely a single class) with a lot of null values or should they be organized in the database in multiple tables (with derived classes), or something else?

Example:

• Widget 1: Small, Blue, Type A, 20 Pounds, Shiny
• Widget 2: Small, Red, Type B, Transparent
• Widget 3: Big, Yellow, Type C, 6 feet, 5 feet, 1 foot

Should they be organized in a table like this, with a lot of nulls:

• Widgits
  • ID, Size, Color, Type, Weight, isShiny, Transparency, width, length, height

Or like this:

• Widgits
  • ID, Size, Color, Type
• Widgits_A
  • ID, Weight, isShiny
• Widgits_B
  • ID, Transparency
• Widgits_C
  • ID, Width, Length, Height

Probably thousands of Widgets in the end, of possibly 20 types.

Thanks!

Answer 1

In the OOP world, what you have implemented in terms of a "Type" property would normally be implemented via inheritance.

Inheritance can be modeled in a database with a parent table (with one record per widget, regardless of type, storing only the fields of the base widget class), and a child table for each widget subtype.

This can make things easier for operations on all widgets (rather than doing a UNION of unrelated widget tables).

However, it can also make things more difficult. For example, to just get all of the fields for one widget, you need to join the record from the parent table with the record from the appropriate child table.

Here's another post on this subject: Table "Inheritance" in SQL Server