database advice

Atomic Pineapple

This is probably a bit of a stupid question but help would be greatly appreciatted.

I have to create an application similiar to carzone.ie or a car search site like that, its only goin to search dealers though, its for a 3rd year software dev program.

I have to do it in java which is fine, using a model view controllr and DAO's I can do this but I'm weak at databases.

I will be using access to create the database and I wondering what people think the database schema should be like?

I was thinking

Table - Cars - has carid, car make, garagelocated

Table - Car_Details - has carid, car model, engine size, engine type, cost

Table - Dealers - has dealerid, dealername, dealer location

so one dealer can have many cars, one car can have many makes

am I goin about this right or have i got it totally wrong? would it be easier to put all the car details in one table?

pwd

I'd suggest something like this:

car: car_id, model_id, garagelocated, colour etc
model: model_id, engine_size, engine_type etc

This way you reduce the database size, complexity and likelihood of data errors because you only have to store the details of the models once, instead of repeatedly for every car of that model in the database.

dzy

Yeah. A car is an instance of a model at a particular dealer.

So I'd have tables :
Cars - CarId, ModelId, DealerId, Price etc.
Models - ModelId, Name etc.
Dealers - DealerId, Name, Address

Atomic Pineapple

ah yes so dealers table would link to the car and then the car would link to the model table?

dzy

Cars contains all car instances. Cars has a foreign key reference into both Models and Dealers since a car instance has one model and one dealer.

You can find out all cars at a particular dealer by joining the Cars table on the Dealers table. You can find all models at a particular dealer by joining the Models table on the Cars table and joining the Cars table on the Dealers table etc.

It seems like the most sensible data model to me.

democrates

Just food for thought since you're learning and other viewers might be interested, to keep a history for the same car appearing for sale on various occasions you could use
dealer_cars : dealerid, carid, price, addDate etc
instead of having dealerid on the car table.

Also you can imagine normalising a bit further;
Manufacturer table for toyota, ford etc,
Model table for corolla, mondeo etc
Feature table for sunroof, aircon, engine cap, fuel type etc
model_feature table to describe available specs in more detail

Then you could go mad and add tables to cater for mods where non-factory extras are added like special alloys, country of first registration, car manufacturer consolidation eg vw buy skoda, and for the ultimate mind-bender keep a history for all these things changing over time using from and to dates throughout.

Anyone curious in database model limitations could search citeseer for papers on spatio-temporal databases, in summary it comes down to choosing a resolution for practical purposes since time and space are continuums and in any event irrational numbers remain in defiance of absolute accuracy.

Attempting absolute normalisation is very niche, back in mainstream reality it's what project goals match resource constraints. What you've got looks fine at a quick glance, just compare it's capabilities carefully with the precise project goals.

Atomic Pineapple

democrates wrote: »

you could use
dealer_cars : dealerid, carid, price, addDate etc
instead of having dealerid on the car table.

Also you can imagine normalising a bit further;
Manufacturer table for toyota, ford etc,
Model table for corolla, mondeo etc
Feature table for sunroof, aircon, engine cap, fuel type etc
model_feature table to describe available specs in more detail

would dealerid not be better off on the car table seeing as one dealer will have many cars, so the dealerid can be applied to many cars??

also whats the best way to set up the relationships between the other tables?

I have a dealer table, make table, model table and spec table so far

democrates

draffodx wrote: »

would dealerid not be better off on the car table seeing as one dealer will have many cars, so the dealerid can be applied to many cars??

also whats the best way to set up the relationships between the other tables?

I have a dealer table, make table, model table and spec table so far

You're quite right, for the job at hand what you've got looks fine.

It was only for the sake of food for thought that I mentioned the possibility of a seperate table for dealer_car, that means if a car came up for sale with one dealer in june and another in december, you'd have a history of both events in your system instead of losing the original dealer when you overwrite the dealerid in the car table.

You can imagine the lengths the government have to go to so they have a history of vrt, motor tax etc. But all that would be overkill, stick with what you've got assuming it meets the spec provided.

Atomic Pineapple

ok have hit a problem, so far have....


Dealer table
-Dealerid(PK)
-DealerName
-Province
-County

Make table
-Makeid(PK)
-MakeName
-Dealerid

Model Table
-Modelid(PK)
-Makeid
-ModelName
-EngineSize
-etc

Dealerid is linked to dealerid on the make table and makeid on the make table is linked to makeid on the model table.

so the problem is that one dealer can have many car makes but only one make of car can be applied to a dealer, so only one dealer can have ford. but i want it so that many dealers can have fords.

is there a way around this or will i have to remove the make table and put the make in the model table?

tiptap

draffodx wrote: »

ok have hit a problem, so far have....


Dealer table
-Dealerid(PK)
-DealerName
-Province
-County

Make table
-Makeid(PK)
-MakeName
-Dealerid

Model Table
-Modelid(PK)
-Makeid
-ModelName
-EngineSize
-etc

Dealerid is linked to dealerid on the make table and makeid on the make table is linked to makeid on the model table.

so the problem is that one dealer can have many car makes but only one make of car can be applied to a dealer, so only one dealer can have ford. but i want it so that many dealers can have fords.

is there a way around this or will i have to remove the make table and put the make in the model table?

Hi,

You're structure isn't really modeled correctly.
You shouldn't have a direct relationship between Make and Dealer at all.

You should have another table which consists of the dealer_id and model_id.

Hope that makes sense

tiptap

Please see attached schema as an example, any questions, just shout


Edit: Actually, you could just have a direction relationship between dealer and model, as only 1 car for sale should really be only sold by 1 dealer.

If it was a case of just wanting to show new cars then you delete mileage/ntc etc etc and use the attached schema.

shortcorner

Just sent you a message with a place to download a sql server version I was working on
Hope it helps

democrates

Would this do:

Dealer table
-Dealerid(PK)
-DealerName
-Province
-County

Car table
-CarId
-ModelId
-DealerId
-Price

Make table
-Makeid(PK)
-MakeName

Model Table
-Modelid(PK)
-Makeid
-ModelName
-EngineSize
-etc