I need some design where I can keep more than one value in one column

Question

I am designing online banking with spring MVC. The customer will have one unique CustomerID but he/she can have more than one account. Like per CustomerID there can be more than one account in the bank. So I am designing a table with attributes:

CustomerID, AccountNumber where CustomerId will be the primary key.

Please suggest how to design the database.

Why can't you use a table with id as primary key, and CustomerId and AccountNumber as the other two columns? — Enbee, Oct 10 '19 at 12:52
You're designing _a_ table which is wrong. You should be designing two tables. — Salman A, Oct 10 '19 at 12:59
Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — philipxy, Oct 11 '19 at 01:17
Time to follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Dozens of published academic information modeling & DB design textbooks are online free in pdf. stanford.edu has a free online course. (But asking for resources outside SO is off-topic.) — philipxy, Oct 11 '19 at 01:17
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. — philipxy, Oct 11 '19 at 01:18

score 2 · Answer 1 · answered Oct 10 '19 at 13:01

Cramming multiple values into the the same field violates the principle of atomicity, and therefore the first normal form. Instead, you should use two tables:

Customer:

CustomerID (primary key)
other customer fields

Account:

AccountNumber (primary key)
CustomerID (foreign key that references Customer table)
other account fields

So, one customer may have one row in the Customer table, and more than one row in the Account table.

blueDexter · Answer 2 · 2019-10-10T12:57:45.430

0

you can use some delimiter like comma (,), semicolor(;) etc to put multiple values in single column. But you can do it in another way. You can create an AccountDetails table where the primary key will be AccountNumber and the foriegn key will be the CustomerId.

edited Oct 10 '19 at 12:57

answered Oct 10 '19 at 12:50

blueDexter

977
11
14

M. Twarog · Accepted Answer · 2019-10-10T13:21:43.647

This is good example in which you could have two tables related to each other - one for Customers and one for Accounts. One customer can have multiple accounts, so it is relation one to many. Consider below tables:

Customers:
CustomerId(primary key), (other columns...)

Accounts:
AccountId, CustomerId(foreign key), AccountNumber

By joining those tables you can have Customers and their Accounts in one view:

SELECT CustomerId, AccountNumber FROM Customers, Accounts WHERE Customers.CustomerId=Accounts.CustomerId

I need some design where I can keep more than one value in one column

3 Answers3