Lookup Wizard or Separate Table

[sara82]

I've been doing a lot of research and reading on databases and normalization and things of that sort because I need to create database from scratch. I've been maintaining a db that someone else made, but it turns out I will have to recreat the whole thing because it's not useful anymore and the users need a more user friendly db. I have a couple of days some are:

tblTO
------
TONumber
Contract
Product Directorate <- combo box 20 items
Document Type <- combo box 15 items
TOManager <- combo box 10 items

tblTOItem
----------
Product <- combo box 13 items
JobNo
BasicDate
ChangeDate
ChangeLevel
TotalBillablePgs
TotalTextPgs
TotalArtPgs
Standard <- combo box 22 items
TemplateVersion <- combo box 20 items
SourceFormat <- combo box 15 items
SourceLocation <- combo box 10 items

The ones I have the arrows for will be a combo box on my form.
My question is should I make each of these field a table of its own?
For instance make the Product field a table and list all the 13 products there?
Or keep the Product field in the tblTOItems and in design view of tblTOItems use the Lookup Wizard and type in my values there to make a lookup column.

I have seen it done both ways, and I was just wondering which way is more efficient.

 

[KenHigg]

I would at least do a seperate table for each of these. The primary reason being the ease at which you will be able to add/edit the items.

Second, if there is anything else you may need to know about a field, say if you need to know anything else about a 'Product Directorate' or a particular 'Document Type', then I'd set them up with a relationship and pk/fk thing. However, if all you ever need to know is just the name of the 'Product Directorate' or the name of the 'Document Type', I'd just use the table as a simple look up table...

 

[sara82]

Ken,

If I want to do the PK/FK thing when for instance I create a Product Directorate table, should I make it the Product Directorate the PK or add another field ProductDirectorateID and make that the PK?

 

[KenHigg]

Close - The ProductDirectorateID field in tblTO would be the fk in that table. It would be the pk in the Product Directorate table.

 

[sara82]

Ken,
Thanks. I understand that the FK is in the tblTO. What I was unsure of this

tblProductDirectorate
ProductDirID (PK) - Autonumber
ProductDirectorate

tblTO
ProductDirID (FK)

or

tblProductDirectorate
ProductDirectorate (PK)

tblTO
ProductDirectorate (FK)

Using the 2nd method I know that I will need to add this In tblTO for the ProductDirectorate (FK) field add this to the rowsource:
SELECT tblDirectorate.ProductDirectorate FROM tblDirectorate;

Then when I create a form from the tblTO I'll have the ProductDirectorate field there with the lookup values.
I am unsure how to do it the 1st way to make the ProductDirectorateID in my tblTO and then how to add the ProductDirectorate values in my form..

 

[KenHigg]

If you're sure that you'll never have a problem with duplicate values in the ProductDirectorate fld, you can use that as the pk. But I would do the autonumber / ProductDirID thing.

Using this method (pk/fk/relationship), you would probably need to do a pop up type form that is used just to edit the tblProductDirectorate table.

Finally, on your form you can do a 2 column combo box to select the 'Product Directorate'. The first column would the bound column and be hidden by giving it a width of 0".

Make sense?