Sql-server – Database design for a car dealers website

database-designdatabase-recommendationsql server

I am creating a new website for all the car dealers in my region to show their cars on and i need your opinion on the database I have created because I have some doubts.

For instance, is it best to have a VehicleID as a foreign key in the TechnicalSpecifications table or should it be the other way around, like having a TechSpecID in the Vehicles table? I am a bit confused and every time I think about it I lose my focus and can't figure it out.

I appreciate your input on my work so far and if you have any comments on anything please do share.

Best Answer

I would say that your second suggestion, haivng a TechSpecID in the Vehicles table, makes the most sense. A way of thinking could be, is a Vehicle implementing a TechSpec or is a TechSpec implementing a Vehicle? I.e. what comes first (the Technical Specification) and what is 'using' your Technical Specifications (the vehicles). But, in the end, it's all down to your context and what makes the most sense in your particular situation.

You have a very broad question so it is difficult to answer specifically what you might be looking for.

One thing I'd like to point out, however, is that most people don't prepend tg_ or fwk_ to their table names. You could use schemas if you'd like to differentiate them some...

Related Solutions

How to query supertypes to get subtype data

Unless you use dynamic SQL to build the query (which adds no value anyway) then SQL is a "fixed column" language. That is, you'd have to send all columns you need. This example is bad in that you have all columns but you get the idea.

SELECT
   V.*,
   C.*,
   B.*
FROM
   Vehicle V
   LEFT JOIN Car C  ON V.VEH_ID = C.CAR_ID
   LEFT JOIN Boat B ON V.VEH_ID = B.BOAT_ID

Another option is to hide the VEHICLE-CAR join behind a view "CARS" and VEHICLE-BOAT join behind another view "BOATS". However, you can't UNION them as the have different columns and types

Basically, you don't query supertypes/subtypes like this because each subtype gives a different resultset.

Relationships in a school database

You might need an intermediate table to store this relationship, as it's possible that a student has more than one parent/gaurdian (a mother and a father) and it's possible that a parent/gaurdian has more than on student.

student
-------
  id


gaurdian
--------
  id

student_gaurdian
----------------
  student_id
  gaurdian_id

The table student_gaurdian can store all the relations between students and gaurdians, and allows many-to-many relations.

RE: your new question

Having two tables with keys to each other looks a little weird to me, but if you really are having performance problems and this relieves them, then it's fine.

Best Answer

Related Solutions

How to query supertypes to get subtype data

Relationships in a school database

Related Question