Database Design Question 1

[dmorse]

I have four drop down boxes I need to fill in:

Category, Engine Family, Bore, and Finish.

Depending on the category, only certain engine families will be available, and then depending on the Family chosen, only certain Bores will be avaiable and the Finish depends on the bore.

Anyway...I am looking for suggestions on how to best design the SQL tables to maintain the relationships. My initial idea is to create a table for each drop down box and then a table with each combination.

Thanks in advance for any words of wisdom.

DMM

 

[r937]

each table should have a foreign key to its parent

create table Category
( id integer not null primary key
, name varchar(9)
)
create table EngineFamily
( id integer not null primary key
, name varchar(9)
, category_id integer
foreign key references Category(id)
)
create table Bore
( id integer not null primary key
, name varchar(9)
, enginefamily_id integer
foreign key references EngineFamily(id)
)
create table Finish
( id integer not null primary key
, name varchar(9)
, bore_id integer
foreign key references Bore(id)
)

rudy

http://r937.com/

SQL Consulting

 

[donutman]

Rudy has the structure correct, but I would name the columns differently. Check out T-SQL Best Practices - PART II - Naming Conventions faq183-5276.
It becomes very confusing when doing INNER JOINS on column names that are NOT the same. There are several other minor points too, but they are covered in the above FAQ. In Rudy's defense, this is a subjective area and therefore just a matter of personal choice.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dmorse]

One thing I forgot to mention is that it is a many to many relation

Category 1 can have engine family 1 and engine family 2 under it and
Category 2 can have engine family 1 and engine family 3 under that.

 

[donutman]

Not a problem as is.

 

[SQLScholar]

But surely if left as is, then there will be repitition and this will not be a rationalised DB. What if 1 bore is for 2000 engines. That one bore will be listed 2000 times.... What if the details change.

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[r937]

if Category and EngineFamily are in a many-to-many relationship, then you must move the foreign key out of the EngineFamily table and create a new relationship (a.k.a. linking or junction) table:

create table Category
( id integer not null primary key
, name varchar(9)
)
create table EngineFamily
( id integer not null primary key
, name varchar(9)
)
create table CategoryEngineFamily
( category_id integer
, enginefamily_id integer
, primary key (category_id, enginefamily_id)
, foreign key (category_id) references Category(id)
, foreign key (enginefamily_id) references EngineFamily(id)
)

do the same for the other m-to-m relationships

rudy

http://r937.com/

SQL Consulting

 

[r937]

regarding karl's faq,

this is a subjective area and therefore just a matter of personal choice

i agree with this statement, and i disagree with more than one suggestion in the faq, so please, take them with a grain of salt, some of those are very subjective



rudy

http://r937.com/

SQL Consulting