SQL Structure for several tables

Question

I need to create a mySQL database that keeps information about vehicles. My instincts were to create one table with as many columns as I need, but then I read about the problems in doing so. After researching, I think I'm on the right track with the following structure:

Vehicles Database

Motorcycles Table
id|road|cruising|touring|

Cars Table
id|sedan|coupe|hatchback|

Colours Table
id|green|red|blue|black|silver|white|yellow|etc..

Make Table
id|ford|chevrolet|gm|toyota|bmw|etc..

Quadrant Table (1-4)
id|motorcycle|car|truck

So basically I have a table for the objects - cars, motorcycles, trucks - and then tables for the fields/properties - Colour, Make, etc. and then a table for the Quadrant the vehicle is seen in, with a value of 1-4 where each row is an instance of only one vehicle.

The problem I'm having is understanding where the primary and foreign keys need to be in order for me to be able to organize the data:

1. By each individual vehicle selected along with its fields
2. By quadrant, showing each vehicle and their respective fields

The user counting cars should be able to input the vehicle type, the field values and the quadrant it's seen in and the db gets populated - and then I need to call the data by quadrant to analyze the data.

I don't know if or how a JOIN statement will be used? How do I go about structuring this database to suit my needs?

FWIW, dba.stackexchange says basic SQL questions belong here, so I hope I'm in the right place.

Answer 1

Can you tell, what is your exact need for the database i.e what functionality you need.

I suggest tables like following:

1) Vehicle table: id|type which might contain info like 1|Motorcycle, 2|Car 2) category table: id(foreign key)|category|color which contain info like 1|touring|Black, 2|Car|Hatchback 3) Make table: (if you need to create another table) id (foreign key to table 1)|Make

I have not understood the functionality of quadrant table but with these 3 table you can create views according to your needs and play around with it.

Answer 2

From my point of view:

I will create a table CarBrands, with columns Id, BrandName, Description, which will serve as a look up.

Then I will create another table Cars with Id, CarBrandId, ColorId (From Colors Table), Description, which is your table with user records.

Same with your other entities. I suggest you search about Entity Relationship Diagrams, a good way of helping you come up with a good design.

Also look at this old StackOverflow question, this will help you.