CODEWITHSUNDEEP
databasedatabase-designrelational-databasedatabase-schema
Guarav T.
Guarav T.

Reputation: 458

Database schema for profile and order

I am working on a bank application in which a customer can open multiple accounts for different product types like: Insurance, Investment and annuity etc.

Each customer has a profile and we are saving that information in 12 different tables such as : Personal,Contact,Address,Affiliation,Financial,Investment,Asset,Liability,NetWorth and so on.

Now to open each account we have to collect and save same kind of information like for customer profile but we cannot overwrite the customer profile with that as we have to keep track of each accounts information when its submitted to the point when it get opened.

So in terms of solution we think either to have replica of these 12 tables for each account or to save the JSON for each table in one table only which has 12 columns for each of above table and populate UI with that.

Can somebody if have prior experience suggest us how to do this in best way.

Upvotes: 1

Views: 294

Answers (1)

Michael
Michael

Reputation: 445

I would determine which information will not change between accounts. For example, name, birthday, ssn, etc. These constant fields can form a table called customers. If there are no constant fields, then you can use a placeholder id, which simply helps to associate accounts to the same user.

I would also create a table for Accounts and use the account_id as a foreign key for all your customer information that may change between accounts. Each row in the Accounts table will be owned by a customer from before.

So the relationship between customers and accounts would be one to many, and the relationship between accounts and "account specific customer info" would be one to one.

Upvotes: 1

Related Questions