CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
Eliran Levi
Eliran Levi

Reputation: 135

How to create relation table with one table to one column relation

Hi there professionals,

I have a question about creating a table for my database, the table's main purpose is to connect between two other tables, I need help with how to design this connecting table.

My DB designed as follows:

TABLE countries COLUMNS country_id, country_name, 
TABLE vehicles COLUMNS vehicle_id, vehicle_category

Currently, in the TABLE country I have 2 countries, and in the TABLE vehicle I have 9 vehicles, probably in the future both tables will contain more countries and more vehicles.

Now I need to connect this 2 tables, so I will create other table, the third table will contain the country_id and the vehicle_id, but here is where my issue begins.

Each country should contain all the vehicles but only once, I mean there isn't a situation where a country will contain 2 vehicles with the same vehicle_id!

How I think to implement the new table's structure (but the danger of duplication still exists!):

TABLE country_vehicle_relation COLUMNS id, vehicle_price, country_id, vehicle_id

Now I can add some values (Let's ignore the price and id columns): 

**country_id 1, vehicle_id 2**
country_id 1, vehicle_id 3
country_id 1, vehicle_id 4
**country_id 1, vehicle_id 2**
country_id 2, vehicle_id 2
country_id 2, vehicle_id 3
country_id 2, vehicle_id 4

So far country_id 2 does not contains any duplication, but country_id 1 has one dangerous duplication, as it grows, it is more bug prone.

So I don't know what is the best approach for this situation, currently I will block this situation with PHP code but I assume that this is not enough and I want the MySQL to be designed on the best side.

I have had this issue earlier with similar table, and for now, I have solved it with my bug-prone solution, but now that this one occurs again, I want to redesign my tables before I deploy the application.

To summarize, I need a table to connect two "Objects", but that one country will contain only one copy of the vehicles in the vehicles table and without having same vehicle twice, also consider that both tables will grow so for now it is 9 vehicles tomorrow it will have 12 vehicles and I will have to add new rows to the table, again without any duplication.

Thank you very much in advance for your answers!

Upvotes: 0

Views: 143

Answers (1)

sacgro
sacgro

Reputation: 469

add UNIQUE Constraint on third table. It will sort out your issue.

ALTER TABLE `country_vehicle_relation` ADD UNIQUE `unique_index`(`country_id`, `vehicle_id`);

Upvotes: 3

Related Questions