CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
Kas
Kas

Reputation: 89

How to merge two tables into one DB schema

I'm new to database logic. I have Owners and Borrowers tables for a rental system. Both of them share similar fields like "email, username, password, etc." and also they differ from each other.
My question is: How can I merge these similar fields into one table, because I'm pretty sure that my implementation is wrong. Another problem is that I can't properly determine relationship between Items and Item_statusesenter image description here

Upvotes: 0

Views: 640

Answers (1)

M. Elghamry
M. Elghamry

Reputation: 322

When you design your tables, you don't organize them according to the similarities among the titles of the columns, but according to the relations between them.

Your design is working, you may just remove the 'item_statuses' table and create a 'status' column in the 'items' table.

But, let's do some brainstorming to improve the design and consider more of the business logic. We should ask some questions like:

  • How frequent will the owner be a borrower?
  • Should I keep track of all the statuses of the item?

If it is frequent for a person to be a borrower and an owner, we will end with duplicated records in our database, so we will make only one table called 'users' with the required fields, and refer the foreign keys in the 'items' table for the users.

If you should keep track of all the statuses of the item, create a separate table called 'items_log' with fields like 'Date' and with a foreign key that references to the item in the items table.

I would also keep track of all the borrowers of the item, so, I can make a 'item_borrowers' table with 'borrower' that references to the user, and 'item' that references to the item with fields like 'Date'.

Also keep in mind that you should store hashed password in the database not plain-text.

Upvotes: 1

Related Questions