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


Problem :

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!



Solution :

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`);

Mysql Tutorials

Mysql Howto..

How to stream resultset with MySQL .net connector for large dataset

How to update table column using array and for loop in php and mysql?

How to insert moment JS time into MySQL

How to make a non-Primary Key a unique valid in mysql? [closed]

how to insert arraylist into single column in mysql database using php

How to evaluate a formula in varchar column of MYSQL Table

How to diagnose intermittently slow PHP to MySQL connection speeds on local network?

How to use cron jobs with php mysql request

How to use Regexp in mysql queries?

how to set triggers with the dynamic values in mysql?

How can I change my Java program to use a database instead of an arraylist?

How to display sql query for forgot password

How to use ucwords() php function on entire mysql database? [duplicate]

How to make partitioning in existing mysql table?

how to write data to mysql database and ignore a column using c#

How to do Pivot in MySQL where only one column is the base of the query

How to store returned result using mysql stored procedure

how to insert multiple fields into mysql

How to connect to mysql from C# over SSH

How to use exclusive locks in MySQL?

how to display the content from MySQL table as the row count equals to multiple of 3 [closed]

Mysql insert data in existing table. how to automaticly give the data new id if already exists

how to count the matched keywords using select in mysql codeigniter php?

How can I change a MySQL column from VARCHAR to date time and convert the data at the same time?

how to code a system to track user learning progress using mysql and php

how to convert mysqli_set_charset($conn,“utf8”) into PDO format in MYSQL?

How to tell when mysqldump is complete

How to change UserDoesNotExist SELECT behavior in Flask-peewee - python & mysql

how to calculate the remaining left time with php and mysql?

How do I avoid MySql deadlocks?