Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Auto data database design

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.
    Last edited by dportas; 04-02-09 at 15:11.

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    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?

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    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?

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •