How to store CarName once and prevent duplicates storage?

Question

I have a table called Person with the following attributes:

I want to implement a table called Car that will hold information regarding the cars a Person drives. I want to implement it in a way that if multiple users drive “Toyota Corolla”, then “Toyota Corolla” should only be stored once as a string. Also it’s only fair that I assume that not everyone owns only one car. The entries will be recorded on a regular basis but the distinct Car records may only reach 5000 only. So far, I have done the following but how can I implement a property that will let me save ModelNames once if multiple user own the same car.

CREATE  TABLE `Car` (
`idCar` MEDIUMINT UNSIGNED  AUTO_INCREMENT ,
`ModelName` varchar(80),
`idPerson` INT NOT NULL,
PRIMARY KEY (`idCar`) ,
FOREIGN KEY (`idPerson`)    REFERENCES `Person` (`idPerson`) 
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE=InnoDB;

UPDATE Car, Person
Set Car.idPerson = Person.idPerson
WHERE Car.idPerson = Person.idPerson;

INSERT INTO Car VALUES (1, 'Toyota Corolla', 1);
INSERT INTO Car VALUES (2, 'Mazda 6', 1);
INSERT INTO Car VALUES (3, 'Toyota Corolla', 2);
INSERT INTO Car VALUES (4, 'Pulsar GTI', 3);


SELECT DISTINCT Car.idCar ,Car.ModelName,  Person.FullName,  Person.idPerson
FROM Person, Car
WHERE Car.idPerson = Person.idPerson
Order by Car.idCar;

Answer 1

I think you need to look at a many to many relation ship design.

Let say

Person
- PersonID -> Primary Key
- Surname
- FirstName
- Title
- etc

Car
- CarID -> Primary Key
- CarType
- CarMake
- YearModel
- etc

PersonCars
- PersonID -> Foreign Key to Person.PersonID
- CarID = > Foreign Key to Car.CarID

This will allow you to define the car definition once, and link it multiple times.

You would then be able to select as follows

SELECT  *
FROM    Person p INNER JOIN
        PersonCars pc   ON  p.PersonID = pc.PersonID INNER JOIN
        Car c   ON  pc.CarID = c.CarID