Best Modelling and Form Design Approach

[HowardB]

Hi All,

I have a type of problem that I want to understand. I have given an example in the attached excel spread sheet with a few comments at the head of each column.

1. I want to confirm that my best modelling approach for this kind of information is a series of 1 to many relationships linking the attributes in columns A->D?

2. In the example given an environmental rating is given to each make/model of car based on each combination of attributes A-->D. How would this work in terms of user form design to enter the necessary information? in order to assign an environmental rating?

Any help greatly appreciated,

regards

Howard B

 

[jdraw]

Starting model based on interpreting your xls

Hope it's helpful

 

[HowardB]

JDraw thanks for the quick reply. Is the approach you drew an example of a star schema?

Would it be a good idea to have a primary key on the Vehicle table that was a composite of the manufacturerID and modelID?

Will the record structure arising from such a schema allow me the flexibility required for a user to assign a environmental code based on multiple combinations of manufacturer, model, engine size and fuel type?

Apologies if you think it is obvious

 

[jdraw]

Nothing is so obvious that an honest question should be ignored.

This is not a star schema as per http://www.learndatamodeling.com/star.php

You could have a composite primary key, but, as you point out, it would have to include all of the pieces you need to make each record unique.

The structure should allow one environmental code per vehicle. If you need multiple (at least more than 1 environmental code per vehicle) then I would suggest a junction table between Vehicle and Environmental code.

 

[HowardB]

OK I will give it a go and get back to you if I have any questions,

thx