I’m going to design a database tables for Customer. Customer has different kind of areas. Please check my design and tell me if there are any issues. Is it ok to use same primary key in all tables.
Which database is this?
If this is a relational database, how do you want to connect the tables?
How do you differentiate between 1:n-relationships that have the same customer, e.g. customer has an income for 2019 and 2020?
I’m going to implement this in MySQL and this was designed by an online tool.
Customer is the main table and all other tables are having relationship with Customer table. Primary key of Customer table is id and other tables also going to have same customer_id.
I think I did a mistake as you said if there are more than one record in a table then I cannot use that primary key. Am I right?
Primary means “primary for this specific table in isolation”.
Customer Income needs some kind of a
Customer Income ID.
I like to be very verbose on this, e.g.
customer_income_id, other people would just name it
id. There are some pros and cons to both approaches.
Then you have to connect the tables, e.g. a Customer needs a relation to all of their Customer Incomes. This is what Foreign Keys are for.
Normally there is no need for several tables if there is not one to many relations. Example the relation between customer and customer_info may always be 1 to 1. There is no need for separate tables IMHO.
-Which are having 1-1 relationship I can merge in to one table.
-If child table’s relationship is 1-many then there should be a new primary key.
customer and customer_info. I see nothing that indicates that there will be several customer_info related to one customer.
Yes, those two can be merged. Thanks all.