homepage Welcome to WebmasterWorld Guest from 50.16.166.175
register, login, search, subscribe, help, library, PubCon, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library : Charter : Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
MySQL database structure question
I have a few tables, not sure which way is the way
Soupisgoodfood




msg:439539		 11:11 pm on Feb 21, 2002 (gmt 0)

Hi. I'm creating a list of cars/makes and models. I have to call a query, and select all the cars makes for a drop down menu, then call another query for the model once the make has been choosen.

I though about having one table for the whole thing. But then, when I want just the list of car makes. I will get duplicates. And same goes for the models (since there are different varients).

The way I though of doing it was by using one of the example I have bellow. They should work. And each I can create a list of makes, all models for a particular make, and all models for a make etc.
But I can't help but think that it looks like a silly way to do it. And that there's a more logical/less redundant way to do it.

Is there a better way? Plese keep in mind that there will be alot of querys to this table (The html/from dropdown menu will be on most pages). So I want to keep it some what effeciant.

Maybe there is there a way to select results that are only unique using MySQL syntax? That way I could just have one table, and when I want to make a list of car makes, just have 'select carModels from cars where carMake = 'blah' [some optional syntax to filter out duplicate models].

Thanks,
Justin.

-----
The example

This:


create table carMakes(
make char(20)
);

create table carModels(
make char(20)
model char(20)
);

create table carVariants(
make char(20)
model char(20)
variant char(20)
);

Or this:


create table carMakes(
makeID int(4) unsigned not null auto_increment primary key,
make char(20)
);

create table carModels(
modelID int(4) unsigned not null auto_increment primary key,
makeID char(20)
model char(20)
);

create table carVariants(
modelID int(8)
variant char(20)
);

 

justa




msg:439540		 11:34 pm on Feb 21, 2002 (gmt 0)

To find the best structure for your database you should go through the steps of Normalisation.

This is a good article outlining the how to's.
http*//www.wdvl.com/Authoring/DB/Normalization/

amoore




msg:439541		 11:36 pm on Feb 21, 2002 (gmt 0)

I would certainly go with the second method.
Also, I would make "make" unique and put indexes on the ID fields.
Although MySQL doesn't impose foreigh keys (yet), you might want to put the definitions in there anyway to help you keep them straight, and with the expectation that they will be supported soon.
Josk




msg:439542		 9:42 am on Feb 22, 2002 (gmt 0)

have you looked at DISTINCT?

select DISTINCT field from table where condition...

But...it may a bit of extra work to do it now, but in the long term having a fully relational database is the way to go. (Imagine having 1000 carvariants, but you only want to pick out the carmodels...)

Earth




msg:439543		 5:53 am on Aug 6, 2002 (gmt 0)

Try using ER modelling and relational mapping. It's the easiest and fastest methodology I've found for relational db design.

The solutions you proposed would/could work - the problem comes with extensibility of design. If you were to ever try to extend your db, or even change the nature of the relationships between the entities (ie your makes, models, and variants) you could run into trouble.

A (relational?) solution to your problem could be:

create table carMakes(
makeid (char20)
);

create table carModels(
modelid (char20)
);

create table carVariants(
variantid (char20)
);

create table makeModel (
makeid (char20)
modelid (char20)
);

create table modelVariant (
modelid (char20)
variantid (char20)
);

All fields are keys - the last two tables have composite keys, using both fields).

That solution assumes that you're going to be able to use a char20 to uniquely identify all makes/models/variants. If this is not ALWAYS going to be the case, you'd be better of having an incrementing autonumber to be the identifying col in each table, with a "description" field or similar for your textual description. You can then query on the autonumber, but display the text description. As you'd be querying on an incrementing number, efficiency may be increased. Also depending on the size of your db, this method may lead to less redundancy - you would store the autonumbers on the "relationship" tables rather than a larger descriptive field doing the same job.

Also, depending on the nature of your data, you may have been better off having one relationship table (in addition to the three "entity" tables) than two I had above (for eg, with nums rather than char - excuse syntax):

create table makeModelVariant (
makeid (int)
modelid (int)
variantid (int)
);

Your query for carMakes would then be simple (using the first table solution):
SELECT *
FROM carMakes

The user selects a car make, 'abc'. Generate a list of models:
SELECT modelid
FROM makeModel
WHERE makeid='abc'

The user selects a car model, 'xyz'. Generate a list of variants:
SELECT variantid
FROM modelVariant
WHERE modelid='xyz'

Distinct wouldn't be necessary in the queries as the fields would have to be distinct....

HTH

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
WebmasterWorld ® and PubCon ® are a Registered Trademarks of Pubcon Inc.
© Pubcon Inc. 1996-2012 all rights reserved