CODEWITHSUNDEEP
databasedatabase-design
001
001

Reputation: 65147

Database design: Custom fee table

Which design would you choose for a database for the custom fee table and why?

Option 1) 1 table, UserId determines if user has custom fee 

ACCOUNT_FEE
TypeId
FixedRate
VariableRate
CurrencyId
UserId (UserId is null for default account fee, UserId is present for custom fee)

Option 2) 2 separate tables, one for default fee the other for user's custom fee

ACCOUNT_DEFAULT_FEE
TypeId
FixedRate
VariableRate
CurrencyId

USER_ACCOUNT_FEE
TypeId
FixedRate
VariableRate
CurrencyId
UserId

Upvotes: 0

Views: 352

Answers (3)

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

I would recommend the single table approach for the custom fees which means you can add more custom fees as they may be needed.

An additional enhancement - consider adding an effective and expiry date to the fee so that is easy to find out when the custom fee is active and allows the useage of additional fees for specific periods of time

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

I would do it differently. Create a user called "Default Account" and give it a specific ID, or even "0" (identity_insert may be required). This allows the table to follow referential integrity rules AND the index on UserID can quickly pick out the Default Account Fee.

Otherwise,

  • using a single table, it will have to table-scan just to get WHERE UserID is null
  • using two tables, you have to keep them separate yet in sync when they really just logically store the same data

Upvotes: 1

richardtallent
richardtallent

Reputation: 35374

I would choose the first design, for the following reasons:

  1. No need to change two tables as the design evolves.

  2. Front-end CRUD code only needed for one table.

  3. Getting a customer's "correct" fee of a particular type is simple:

    SELECT TOP 1 * FROM ACCOUNT_FEE
WHERE  
  (UserId=333 OR UserId IS NULL)
  AND TypeId=2
ORDER BY UserId DESC

Upvotes: 0

Related Questions