Database Design

Help database design

Please support our Database Design advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 3

Reputation: starter is an unknown quantity at this point

Solved Threads: 0

starter

Offline

Newbie Poster

Help database design

Nov 28th, 2008

Hi
I am trying to insert data into 1 of my table and I get this error:
Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 fields to Null due to type conversion failure, and it didn't add 1 records to the table due to key violation, 0 records due to lock violation, and 0 records due to validation rule violation.

My design is:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE TABLE MANUFACTURER(
Manuf_name CHAR(16) NOT NULL,
Manufacture_id VARCHAR(2) NOT NULL,
PRIMARY KEY (Manufacture_id));
 
CREATE TABLE MODELS(
Model_name VARCHAR(16) NOT NULL,
Model_id VARCHAR(2) NOT NULL,
Manufacture_id VARCHAR(2) NOT NULL,
PRIMARY KEY (Model_id),
FOREIGN Key (Manufacture_id) REFERENCES MANUFACTURER);
 
CREATE TABLE OPTIONS(
option_name VARCHAR(16) NOT NULL,
option_id VARCHAR(2) NOT NULL,
PRIMARY KEY (option_id));
 
CREATE TABLE CAR(
YEAR VARCHAR(4) NOT NULL,
car_id VARCHAR(3) NOT NULL,
color CHAR (20) NOT NULL,
Manufacture_id VARCHAR(2) NOT NULL,
Model_id VARCHAR(2) NOT NULL,
stock_num VARCHAR (3) NOT NULL,
price FLOAT NOT NULL,
mileage VARCHAR (10) NOT NULL,
Vin_num VARCHAR (7) NOT NULL,
PRIMARY KEY (Vin_num),
FOREIGN Key (car_id) REFERENCES CAR_OPTIONS,
FOREIGN Key (Manufacture_id) REFERENCES MANUFACTURER,
FOREIGN Key (model_id) REFERENCES MODELS);
 
CREATE TABLE CAR_OPTIONS (
car_id VARCHAR(3) NOT NULL,
option_id VARCHAR(3) NOT NULL,
PRIMARY KEY (car_id),
FOREIGN Key (option_id) REFERENCES OPTIONS);
 
error occurs if i try to INSERT something LIKE: 
 
INSERT INTO CAR (YEAR,car_id,color,manufacture_id,model_id,stock_num,price,mileage,vin_num)
VALUES ("2008","1","Blue","1","2","12","13000","20000","12398");
Create table MANUFACTURER(
Manuf_name char(16) Not Null,
Manufacture_id Varchar(2) Not Null,
Primary Key (Manufacture_id));

Create table MODELS(
Model_name varchar(16) Not Null,
Model_id Varchar(2) Not Null,
Manufacture_id Varchar(2) Not Null,
Primary Key (Model_id),
FOREIGN Key (Manufacture_id) REFERENCES MANUFACTURER);

Create table OPTIONS(
option_name varchar(16) Not Null,
option_id Varchar(2) Not Null,
Primary Key (option_id));

Create table CAR(
year varchar(4) Not Null,
car_id Varchar(3) Not Null,
color char (20) Not Null,
Manufacture_id Varchar(2) Not Null,
Model_id Varchar(2) Not Null,
stock_num varchar (3) Not Null,
price float Not Null,
mileage varchar (10) Not Null,
Vin_num varchar (7) Not Null,
Primary Key (Vin_num),
Foreign Key (car_id) references CAR_OPTIONS,
Foreign Key (Manufacture_id) references MANUFACTURER,
Foreign Key (model_id) references MODELS);

Create table CAR_OPTIONS (
car_id Varchar(3) Not Null,
option_id Varchar(3) Not Null,
Primary Key (car_id),
Foreign Key (option_id) references OPTIONS);

error occurs if i try to insert something like: 

Insert into CAR (year,car_id,color,manufacture_id,model_id,stock_num,price,mileage,vin_num)
Values ("2008","1","Blue","1","2","12","13000","20000","12398");

This occurs when I try to add data into the car or CAR_OPTIONS table
Please help..

Last edited by peter_budo; Nov 29th, 2008 at 6:10 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Jun 2005

Posts: 60

Reputation: pclfw is an unknown quantity at this point

Solved Threads: 5

pclfw

Offline

Junior Poster in Training

Re: Help database design

Jan 9th, 2009

Originally I thought that this was in the wrong forum. But I was wrong.

Your query regarding the foreign key violations should be fairly trivial if you trace back through all of the tables ensuring that the correct data is in place BEFORE you try to insert the CAR row. Also you might want to have a look at the CAR_OPTIONS table. It has a PK on the CAR_ID value. PK's are (usually) unique, while the name CAR_OPTIONS implies multiple rows for each car. This would give rise to the Key violation error you report.

However now the Database Design problem.

Looking at your SQL code I see that you have FK releationships from the CAR table to the MANUFACTURERS table and the MODELS table. Surely a MODEL is owned by the MANUFACTURER and you then only need to FK to the MODELS table. Yes; different car models MIGHT have the same name, bu they WILL have different MANUFACTURERS and thus a different MODELS.MODEL_ID.

I do not understand the OPTIONS and CAR_OPTIONS system(s). An option for a car (real world) may have a name shared by many other options. i.e. 'Metalic Paint' or 'Air Conditioning'. While the NAMES of these options may be the same from manufacturer to manufacturer, the actual OPTIONS themselves are not. Thus I would have expected the OPTIONS table to include the MODEL_ID and be FK'ed on this. Then I would expect the CAR_OPTIONS to just hold the CAR_ID and the OPTION_ID. FK's can be placed on both of these columns but are not usually necessary.

Hope that you can understand my meanderings and that they help.