SQL Table Organization

Question

I have verious accounts which need to be kept track of. These accounts are all very similar having fields such as:

account_number, date_opened, current_expenditure etc.

Now, these accounts are of three types (we'll call them Type A, B, C) Each of these account types requires at least one other field, unique to it's type. For example:

Type A: account_number, date_opened, current_expenditure, owner
Type B: account_number, date_opened, current_expenditure, location
Type C: account_number, date_opened, current_expenditure, currency

My question is whether or not I should combine these into one large table, with a column denoting account type (leaving irrelevant fields empty):

Table 1: accounts
Table 2: accts_emp_maps

Account Columns: 
account_number, type, date_opened, current_expenditure, owner, location, currency

Or, should I have an individual table for each account type? Keep in mind that there will be other tables mapping employees to these accounts. If I split up the accounts into different types, I will need to split the maps as well. IE:

Table 1: A_accounts
Table 2: A_accts_emp_maps
Table 3: B_accounts
Table 4: B_accts_emp_maps
Table 5: C_accounts
Table 6: C_accts_emp_maps

score 1 · Accepted Answer · answered Jun 12 '12 at 13:33

1

I'd go for the one table approach, with owner, location, currency, extra columns. It'll make your life easier.

If it gets too large, you can partition it by type.

answered Jun 12 '12 at 13:33

bpgergo

15,669
5
44
68

That's what I was thinking. But I have to balance doing it the "right way" and doing it the logical/easy way – Peaches491 Jun 12 '12 at 13:56

score 1 · Answer 2 · edited May 23 '17 at 12:02

Classically, you'd maybe use the superkey/subtype pattern for this to ensure only one of owner, location, currency

One table with common columns and a type column
Unique super key on the PK + type (A, B or C) column
Three child tables with key of PK + type. This is where you add specific columns
Check constraint on the child type constraints to limit to A, B or C in the child tables

Now, in this case I'd consider having redundant columns for simplicity though

Examples:

score -1 · Answer 3 · answered Jun 12 '12 at 13:34

Of the two options you have listed, I would definitely pick the first one. It will be fine for most applications, and the tables will be simpler to query manually. (The second proposed design duplicates a lot of information among the three sets of account tables).

However, depending on your needs, a more normalized database design which may be better is something like this:

Table: accounts
===============
number, type, date_opened, current_expenditure

Table: account_owners
=====================
account_number, owner

Table: account_currencies
=========================
account_number, currency

Table: account_locations
========================
account_number, location

You now have the problem of enforcing exactly one row in one of the child tables — gbn, Jun 12 '12 at 13:38

SQL Table Organization

3 Answers3