Hello Everyone,

I look at an automotive sales report each month that lists number of cars sold by brand. The hierarchy of this data is as such:

Parent company -> Make -> Type -> Model

One 'parent company' (ex. GM) will have many 'makes' (ex Buick, or Cadillac, or Chevrolet, etc). One 'make' has one of two 'types' (car or truck), and for each type there are several models (1 Series, 3 Series, etc).

Each month I would like to add the new sales data when the report is issued.

I am wondering if I should create a new table for each parent company, and in the table have fields 'Make', 'Type', 'Model' and 'Sales'. Is this the most logical and efficient way to structure this data?

Any suggestions are more than welcome. I appreciate the help in advance.

One table for all companies makes most sense because it avoids the possibility of redudant data and makes it much easier to analyse the data across all companies.

Thank you for the quick reply. So should I create the rows 'id', 'parent company', 'make', 'type', 'model', 'date' and 'sales' and create a new column each time the monthly data is released (to update the 'date' and 'sales' values)?

Much of the data will be the same every single month - 'parent company', 'make', 'type', and 'model' will never change. Is there a better way to structure this information, or is the way stated above the best for this type of job?

Do you need to keep a history of the changes or just the latest data set? Either way you don't need to keep duplicating rows if they don't change - just have a ValidFrom date and a ValidTo date.

Sales I would expect to go in another table with a key that includes the date because that is presumably your history of previous months' sales.

It is important that I keep a history of past sales data and the associated dates. Would it be smart to make four tables organized like so:

a 'parent company' table with 'parentID'
a 'make' table with 'makeID'
a 'model' table holding type and model information
a 'sales' table holding date and sales information

and connect them with the appropriate keys? Is there a better way to do this, or is this an acceptable, scalable design for this project?

And, No. Do NOT create a new column each time a month is added. That would be a big mistake. Include the month in the table definition, and add a new record with the month and the sales data.