CODEWITHSUNDEEP
sqldatabase-design
Peaches491
Peaches491

Reputation: 1239

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

Upvotes: 0

Views: 1867

Answers (3)

gbn
gbn

Reputation: 432672

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:

Upvotes: 1

We Are All Monica
We Are All Monica

Reputation: 13354

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

Upvotes: -1

bpgergo
bpgergo

Reputation: 16057

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.

Upvotes: 1

Related Questions