normalizing question

[Guest]

I'm not sure what would be the proper normalized format for this:
I am storing vehicles to be sold; I have a table that stores the vehicle
ID(PK), year, make, model, serialnumber, price, owner ID (FK from owners
table), status ID (FK to status table), addtl info ID (FK to addtl info
table).

The question is, in the addtl info table, I store the options of the car
(Air, tilt, cruise, etc.). These are check boxes. Is there a more normalized
way to store these than having this: car #1: air Y, tilt N, Cruise N, P/Win
Y....etc.? Should I have a table for options, and a multi-seelect in the
form? How would that store?

Also, I want to store a link to a photo of each car. Should I put that in a
new table, the main table, or the addtl info table?

 

[Damon Heron]

It might be a good idea to have an options table -ID, OptionName. This way
if new options come along that aren't on your list, then you won't have to
redesign the forms to accomodate the new option. I would use a multi-select
list box tied to the option table and save the choices selected in the
additionalinfo table by FK OptionID.
As for the pic, I would include that on the first table with the car info on
it. In the form, I would have a button to view the pic if desired, rather
than loading the pic automatically - that will speed up the process of form
loading.

HTH
Damon

 

[Guest]

Thank you for your reply.

So, if I have an "option" table, and "air"=1, "tilt"=2, "cruise"=3 etc., and

 

[Damon Heron]

Look at the ItemsSelected property in VBHelp that has a couple of examples
for retrieving the items selected from a list box. The list box has two
columns (or more) and the common name would be the one visible - like
CruiseControl. The OptionID is the one you need to store though, not the
name. Usually, a command button is placed next to the listbox, so when the
user is finished selecting/deselecting, the command button runs the code in
the examples. You could save the selection in an array, and then open a
recordset and save the results (which would be the OptionID and VehicleID)
in your "additionalinfo" table at the same time, depending on how your data
entry is set up. You can retrieve the info in a variety of ways, queries,
reports, forms- as long as you establish the relationships between the
tables correctly.

Damon

 

[Allen Browne]

One vehicle can have many options.
One option can appear on many vehicles.
You therefore have a many-to-many relation between vehicles and options.

That means you need 3 tables:
- Option table (one record for each kind of option, with an OptionID primary
key)
- Vehicle table (one record for each car, with a VehicleID primary key)
- VehicleOption table, with fields:
VehicleID relates to Vehicle.VehicleID
OptionID relates to Option.OptionID

So, if you want to record that vehicle 24 has 3 options, it will appear in 3
*records* in this table, e.g.:
VehicleID OptionID
24 air
24 cruise
24 tilt

The interface is a main form bound to the Vehicle table, with a subform
bound to the VehicleOption table. The subform is in continuous view, so you
can see multiple options, one per row. The subform has a combo (Option table
as Row Source) so the user can choose from the list.

The 3rd table here is referred to as a junction table. This is the standard
way of resolving a many-to-many relation into a pair of one-to-many. For
another example, see:
http://allenbrowne.com/casu-06.html

Regarding the photo, do you want:
a) A photo of every individual photo (right color, licence plate, etc), or
b) A generic photo showing the kind of vehicle (e.g. Ford Mondeo)?

If (a), the photo goes in the Vehicle table.
If (b), you will have another table of VehicleTypes, and the photo goes in
there.

Either way, you want to store a link to the photo, rather than embed the
photo in the database. That's dead easy in Access 2007: just set the Control
Source of the image control to the field containing the fully qualified
field name. For earlier versions, see:
http://www.mvps.org/access/forms/frm0044.htm

 

[Guest]

Just what the doctor ordered. Thank you very much.