SQL Table Organization
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
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.
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
- A constraint that only allows one of two tables to reference a base table
- (DBA.SE) https://dba.stackexchange.com/questions/4105/database-schema-with-a-shared-identity-field/4110#4110
- Database design - articles, blog posts, photos, stories
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