CODEWITHSUNDEEP
sqlreactjspostgresqldatabase-design
Dave
Dave

Reputation: 103

Database SQL design

I'm making a React app, and now I need to store IBAN number, bank_name, bank_location etc... for every user, and every user can have only 1 bank.

Currently I have one table that describe a User,

User(uid, name, lastname, bio, url_img, ..)

and now that I have to add new information do you think that is a good idea to create a new table for bank information or is better to put the new info in the User table?

Idea 1:

User (  uid
      , name
      , lastname
      , bio
      , url_img
      , IBAN_number
      , bank_name
      , bank_location
      )

Idea2:

User (uid, name, lastname, bio, url_img)

Bank_user_info(
       id
     , uid_user
     , IBAN_number
     , bank_name
     , bank_location
     )

is the idea 2 a good idea even a user will have maximum only just one bank_user_info row???

Upvotes: 0

Views: 365

Answers (3)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

First case leads to logical errors, essentially a user can not exist without a bank. Given that a single bank can have may clients, you are likely to keep redundantly repeating banking info and eventually resulting in contradicting information. Redundancy leads to contradiction, sooner or later.

In the second case user is independent, but (logically) bank does not exist without a user, and same redundant banking info problem as in the first case.

So, I would recommend

-- only attributes relevant to a user
--
user {USER_ID, ...}
  PK {USER_ID}

-- only attributes relevant to a bank
--
bank {BANK_ID, ...}
  PK {BANK_ID}


-- User banking detail (only one bank per user)
--
user_bank {USER_ID, BANK_ID, ACCOUNT_NO, ... }
       PK {USER_ID}
       AK {BANK_ID, ACCOUNT_NO}

FK1 {USER_ID} REFERENCES user {USER_ID}
FK2 {BANK_ID} REFERENCES bank {BANK_ID}

And if a user can bank with more than one bank:

user_bank {USER_ID, BANK_ID, ACCOUNT_NO, ... }
       PK {USER_ID, BANK_ID}
       AK {BANK_ID, ACCOUNT_NO}

FK1 {USER_ID} REFERENCES user {USER_ID}
FK2 {BANK_ID} REFERENCES bank {BANK_ID}

Notes:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key

Upvotes: 1

Fatih Develi
Fatih Develi

Reputation: 105

This answer explains when to use a new table or a new column. In your case, IBAN number can be an attribute of the User table, however other columns should be a part of the Bank entity. This prevents the replication of bank information (name, location) for each user. So the tables can be like:

User(uid, name, lastname, bio, url_img, IBAN, bank_id)
Bank(id, bank_name, bank_location)

Upvotes: 0

Pepi
Pepi

Reputation: 171

With the bank table holding info about user id, the two table would have to be equal size, so two tables would be pointless. Since 1 bank will (in the real world) have more than 1 user, disk space will be saved by using a separate table for the bank info, without losing any data. This is the basic idea of a relational database.

Reconsider the fields you have proposed for your tables. The user table should have the unique info about each user, plus an id to identify their bank. The bank info table will contain that same id, and whatever else is unique to each bank.

Now that disk space is cheaper, you might consider putting everything in one table, if it isn't too unwieldy. But...suppose a bank has 1,000,000 customers and then changes its name. Would you rather update 1,000,000 rows in one giant table, or 1 row in the bank info table?

Upvotes: 0

Related Questions