What is wrong with this data structure?

Question

I have been asked to describe what it wrong with this data structure, and how I would improve it.

Here is the data structure:

Here is what I have so far:

• The Car price is set only if the car is in the showroom, it would make more sense to put the price of the car in the car table

• It makes no sense to store NULL data in the Car Table, it would be better to have a layout similar to this:

  

• There needs to be a quantity heading to show how many of that particular car are in the showroom as some showrooms have multiple of the same cars

The new table I drew up still has repeating data, which I vaguely remember is a no no when drawing up a data structure, and so I think I need to make a 3rd table? I'm really not sure.. .

I just need a bit of help as to what is wrong with the current data structure and if there is any way to improve it, any help is appreciated.

Answer 1

One problem is that the Car table stores two distinct things - it stores makes, and it stores models.

So you should split that up, something like:

Makes: columns makename, makecode

Models: columns makecode (foreign key for makes), modelname, modelcode

And now showroom table will only relate to models, so it can't reference a make by mistake.

Since one model can have many showroom table rows related to it, you can't merge the two tables meaningfully, so keep them separate and go from there.