Performance Typed Column x Distinct Table

Question

There are differences between distinct tables and type columns in terms of Performance or Optimizations for queries?

for example:

Create Table AllInOne(
  Key Integer Identity Primary Key,
  Desc varchar(20) Not Null,
  OneType Integer Not Null
)

Where OneType only receives 1,2 or 3. (integer values)

Versus the following architecture:

Create Table One(
  Key Integer Identity Primary Key,
  Desc varchar(20) Not Null
)

Create Table Two(
  Key Integer Identity Primary Key,
  Desc varchar(20) Not Null
)

Create Table Three(
  Key Integer Identity Primary Key,
  Desc varchar(20) Not Null
)

Another possible architecture:

Create Table Root(
  Key Integer Identity Primary Key,
  Desc varchar(20) Not Null
)

Create Table One(
  Key Integer Primary Key references Root       
)

Create Table Two(
  Key Integer Primary Key references Root     
)

Create Table Three(
  Key Integer Primary Key references Root    
)

In the 3rd way all data will be set in the root and the relationship with the one, two and three tables.

I asked my teacher sometime ago and he couldn't answer if there is any difference. Let's suppose i have to choose between these three approaches.
Assume that commonly used queries are filtering the type. And there are no child tables that reference these.

To make it easier to understand let's think about an payroll system.
One = Incomings
Two = Discounts
Three = Base for calculation.

Depends what your queries are. Besides performance, there is also another (more serious difference). In the second design, you can't have a Foreign Key that references the `AllInOne` table, you can only reference any of the 3 tables individually. And the other way around. — ypercubeᵀᴹ, Feb 26 '13 at 13:32
Agree with ypercube. If you follow this argument to it's logical conclusion, you'd only ever have one table in any database, just with a lot of columns... — Paddy, Feb 26 '13 at 13:58

score 1 · Accepted Answer · answered Feb 26 '13 at 21:08

Having separate tables, like in (2), will mean that someone who needs to access data for a particular OneType can ignore data for other types, thereby doing less I/O for a table scan. Also, indexes on the table in (2) would be smaller and potentially of less height, meaning less I/Os for index accesses.

Given the high selectivity of OneType, indexes would not help filtering in (1). However, table partitioning could be used to get all the benefits mentioned above.

There would also be an additional benefits. When querying (2), you need to know which OneType you need in order to know which table to query. In a partitioned version of (1), partition elimination for unneeded partitions can happen through values supplied in a where clause predicate, making the process much easier.

Other benefits include easier database management (when you add a column to a partitioned table, it gets added to all partitions), ans easier scaling (adding partitions for new OneType values is easy). Also, as mentioned, the table can be targeted by foreign keys.

That's great! There is it for oracle too?? – EProgrammerNotFound Feb 27 '13 at 10:47 — EProgrammerNotFound, Feb 27 '13 at 10:47
You mean partitioning? Yes, very much so – muhmud Feb 27 '13 at 11:16 — muhmud, Feb 27 '13 at 11:16

Performance Typed Column x Distinct Table

1 Answers1