Question
-
Topic
-
Access 2007 – How do I choose the right structure for my database?
LockedI am trying to use Access 2007 to store data with many relationships and am trying to determine the best way to store this data. I am also creating quite a complex UI (in Access) to use/store this info and send info to and from Excel for Automotive Industry.
I am trying to build a model of a complete car in Access and wish to select from a list of different size gears to put into a gearbox. However, I have a choice of 1st gears etc for different cars. So once I’ve chosen the car type, I would then like to choose a 1st gear based on a list of car-specific 1st gears. I am able to do this with a combobox on a form. However, I’m not sure of the best way to store this info as this type of problem is repeated in greater complexity across the database. For example, not every car has 5 gears and some have other unique parts in the gearbox that also have values associated with them. I currently have a special ‘options’ table that I query to determine the car-specfic list. However, I’m really struggling the determine the best way of storing and using my data.Table1: GearOptions
GO_ID (PK)
car name (FK)
Part name (eg 1st gear)
Part Value (eg ’12/36 teeth)Table2: Gearbox
Gb_ID (PK)
car name (FK)
1st gear value (choosen from combobox on form from list generated from Table 1 above)
2nd gear value (etc..)I would be very grateful if anyone can help with the structure of my database.