Alpha Video Training
Results 1 to 9 of 9

Thread: Set Design?

  1. #1
    Member Rayr's Avatar
    Real Name
    Ray Roosa
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    427

    Default Set Design?

    Hi All,
    I need to create a vehicle application that stores year, make, model as well as a parts table, my ? is should I create two or three tables to hold the vehicle info such as one table for the years, and one table for the makes and yet another table for the models?

    Or should I just make two table, one for the makes, and one that would hold
    the year and model for that make?

    My Main application would need to lookup the year, make, and model. I think this is the best set design but I would like others opinion on this set design.

    I understand linking and creating sets I just dont know if I should create two
    or three tables for the year, make, and model part of my application, and yes
    I now I will have to create yet another table or tables for the parts.

    I have decided this application is small enough to get me started with Alpha Five and I desperately need a vehicle database so this project is perfect for relearning Alpha Five as I have only dabbed with it over the years.

    Thank you
    Ray
    Kind Regards,
    Ray Roosa
    Raylin Micro, LLC.

  2. #2
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default

    Ray,

    You are a bit vague with what type of database you need. I know you said "vehicle application that stores year, make, model as well as a parts table", but that doesn't really tell me what I would need to answer your question directly, so here is a indirect answer.

    Assuming you want a parts list for a particular make, model and year, then my thought would be you need a parent table that holds the make, model and year of the vehicle and then a child table 1:M that holds the parts for that parent.

    If you want it to track an existing fleet of vehicles you might want to get a bit more creative and store your vehicle ID, make, model, year, color ect. in your parent and create lookup tables for the make, model, year,color, trim type ... on and on .... but you would still use a 1:M child table for parts ... you could also use a 1:M child for maintenance records ... trips .. gas purchases ... again .. on and on ....

    Hope this helps you some ...

    Scott

  3. #3
    Member Rayr's Avatar
    Real Name
    Ray Roosa
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    427

    Default

    Quote Originally Posted by Scott Emerick
    Ray,

    You are a bit vague with what type of database you need. I know you said "vehicle application that stores year, make, model as well as a parts table", but that doesn't really tell me what I would need to answer your question directly, so here is a indirect answer.

    Assuming you want a parts list for a particular make, model and year, then my thought would be you need a parent table that holds the make, model and year of the vehicle and then a child table 1:M that holds the parts for that parent.

    If you want it to track an existing fleet of vehicles you might want to get a bit more creative and store your vehicle ID, make, model, year, color ect. in your parent and create lookup tables for the make, model, year,color, trim type ... on and on .... but you would still use a 1:M child table for parts ... you could also use a 1:M child for maintenance records ... trips .. gas purchases ... again .. on and on ....

    Hope this helps you some ...

    Scott
    Scott,
    Thank you for your response, but should I only have one table for this? I think I should have one table for Make and another table for year and model, what are your thoughts?

    Ray
    Kind Regards,
    Ray Roosa
    Raylin Micro, LLC.

  4. #4
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default

    Ray,

    You still don't get specific on what exactly you need, but here is my thought.

    You will have parts for a 2001 Chevy S10 Blazer so one table that holds "2001 Chevy S10 Blazer" is your parent record which then can be connected to several parts for the "Year, Make and Model"

    Breaking it out to a table for Make and then another for model doesn't make sense to me.

    Why do you think you need to separate this?

    Scott

  5. #5
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default

    Quote Originally Posted by Rayr
    Thank you for your response, but should I only have one table for this? I think I should have one table for Make and another table for year and model, what are your thoughts?
    OK, "R" tells us not to duplicate information, and I presume that's where you get the notion that you should have a table for "make", but is that really true in this case?

    I mean, let's say, you have a table for "Make" as:

    Code:
    Dodge     0001
    Ford      0002
    Chrysler  0003


    And then dutifully, in your parts table, you use "0001" instead of "Dodge". What have you gained? Well, in case you ever need to change "Dodge" to something else, you're set.

    But, of course, that's never going to happen, since you're dealing with past identities. A '72 Dodge Dart is always going to be a '72 Dodge Dart. Now, if you're going to store more information about Dodge (hardly seems relevant for an auto parts app, but hey...) you would definitely want to store it in a different table.

    But even in that case you might arguably keep using the actual Make field as a link ("Dodge", "Ford", "Chrysler") instead of the unique ID because, again, it's never going to change.

  6. #6
    Member Rayr's Avatar
    Real Name
    Ray Roosa
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    427

    Default

    Quote Originally Posted by Blake
    OK, "R" tells us not to duplicate information, and I presume that's where you get the notion that you should have a table for "make", but is that really true in this case?

    I mean, let's say, you have a table for "Make" as:

    Code:
    Dodge     0001
    Ford      0002
    Chrysler  0003


    And then dutifully, in your parts table, you use "0001" instead of "Dodge". What have you gained? Well, in case you ever need to change "Dodge" to something else, you're set.

    But, of course, that's never going to happen, since you're dealing with past identities. A '72 Dodge Dart is always going to be a '72 Dodge Dart. Now, if you're going to store more information about Dodge (hardly seems relevant for an auto parts app, but hey...) you would definitely want to store it in a different table.

    But even in that case you might arguably keep using the actual Make field as a link ("Dodge", "Ford", "Chrysler") instead of the unique ID because, again, it's never going to change.
    Blake, Scott
    Ok so your saying make one table for everything holding year, make, model
    But what if I want to create a form for viewing my info and I want to see all models for ford? this seems easyer if I put the make in a sep table.

    What I am going to do is create a tree control on my form so the user can select ford then under ford they select the model then under the model they select the year, so should I still only use one table or brake it up into two?

    What are your thoughts?

    Thank you
    Ray
    Kind Regards,
    Ray Roosa
    Raylin Micro, LLC.

  7. #7
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default

    Ray,

    A table holds records which are made up of 1 or more fields. Records can be sorted, filtered, queried based on field values.

    But what if I want to create a form for viewing my info and I want to see all models for ford?
    You filter or query the TABLE to show only the RECORDS which has a value of FORD in the make FIELD

    If you want to see all 1997 Fords you filter or query the TABLE to show only the RECORDS which has a value of FORD in the make FIELD and the value of 1997 in the year field.

    Creating a tree uses the field values as well. Your tree might be set up as Year, Make, Model:

    1997^Ford^Explorer
    1997^Ford^Mustang
    1997^Chevy^Blazer
    1997^Chevy^Blazer, S10

    I hope you understand what I am saying.

    Since it is a small enough application, you can do it this way and also try it the way you think you should have it and see what works for you.

    Good Luck!!

    Scott

  8. #8
    Member
    Real Name
    Robert Picard
    Join Date
    Apr 2000
    Posts
    326

    Default

    Hi Ray

    I developed an automotive application several years ago to track the salesman's performance.

    I am not sure as to what you want to accomplish. As stated by others if we know exactly what the end result of application will be and the reports needed we can better help.

    That being said here is what I did.

    The car info associated with the salesperson was in one table. A look up table was created for the cars. This had 3 fields. Make (ford), Type (suv/van etc), Model (taurus).

    I did not include the year in the lookup table. This creates to many entries in the lookup and this information is not relevant for a look up. You only need to enter the year for you car in the info table.

    The look up table was filtered based on fields and values in the info table.

    1. Make field would filter the lookup table for Unique Makes only.
    2. Model field would filter the lookup table showing models filtered by the value in the model field.
    3. Type field would filter the lookup table showing type filtered on Unique types.

    You can also use your lookup table as the Parent in a one to many relationship with you info table for reporting purposes.

    You probably already realize you will need a seperate parts lookup table. Here the year is important and needs to be included. As this will allow you to filter Parts for differenct model years.

    I hope you find this helpful

    Robert

  9. #9
    Member Rayr's Avatar
    Real Name
    Ray Roosa
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    427

    Default Thank You

    Thank you for all your help.

    -Ray
    Kind Regards,
    Ray Roosa
    Raylin Micro, LLC.

Similar Threads

  1. Set Design
    By paulyp in forum Alpha Five Version 6
    Replies: 1
    Last Post: 08-17-2004, 05:50 AM
  2. Set Design
    By johngt in forum Alpha Five Version 5
    Replies: 12
    Last Post: 07-09-2003, 06:35 AM
  3. Set Design Help?
    By David Agan in forum Alpha Five Version 5
    Replies: 1
    Last Post: 06-27-2003, 04:18 AM
  4. help set design
    By vasant panjabi in forum Alpha Five Version 4
    Replies: 1
    Last Post: 10-17-2000, 11:35 AM
  5. Help with Set Design?
    By Tom Sullivan in forum Alpha Five Version 4
    Replies: 2
    Last Post: 04-03-2000, 11:17 AM

Bookmarks

Posting Permissions

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