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

Answers


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

Examples:


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

Need Your Help

arrange by two factors

r ggplot2 r-factor

I have been trying the whole day to arrange two factor levels called "type" and "name" by a numeric value called "score", and plot by category type (with color determined by type) ordered by score....

RoR syntax highlighting for Coda 2

ruby-on-rails syntax-highlighting coda

I am curious how to get highlighting for RoR syntax. For instance the Gemfile is all one color and difficult to read.