MySQL Forums
Forum List  »  Newbie

Question About Database Structure
Posted by: Ross Peoples
Date: June 10, 2005 12:06AM

I have spent all day trying to get an answer to two questions and I think I found the answers, but I need some clarification.
First Question:

How would you create a table (car) with a colum that references another table (carMake) for the value. This is the answer I found:

CREATE TABLE car
(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
makeOfCar INT UNSIGNED NOT NULL REFERENCES carMake(id)
)TYPE = InnoDB;

CREATE TABLE carMake
(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
makeName VARCHAR(10) UNIQUE NOT NULL
)TYPE = InnoDB;

And how would you return Car(1).makeOfCar = "Ford"

SELECT car.* FROM car, carMake
WHERE car.id = '1';

Is that right?

My next question is how would you store the cars in a table (owner)? The main answer I got on that one was that you can, but you can't. In other words, you can make it reference like above, except you use:

FOREIGN KEY ownedCars (ownedCars) REFERENCES car(id);

And the only difference from the implementation above is that you can maintain the integrity by using ON UPDATE CASCADE. On other words, you can't actually store multiple references to the table (car) in owner.

Is that right? And if so, would you just store the id's of the cars into the ownedCars column as a comma-seperated string and manually retreive the car records later?

I am very new to programming with MySQL. I just started writing scripts today that create tables. I have no problem creating basic tables, but I haven't even had a solid database to practice SELECT calls on, so I am very new to the data manipulation commands.

Sorry for such a long post, but any help would be greatly appreciated.

Options: ReplyQuote


Subject
Written By
Posted
Question About Database Structure
June 10, 2005 12:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.