Limited possibilities for field - best practice?

[drmweaver2]

I'm sure this is such a basic question that some of you will laugh, so please "be gentle"... Lol

Okay, here's the deal. I am designing a system where 2 different tables (Item, Accessory) will have a field with one of the same 8 "condition values" and only one of those 8 "condition values" (ex., Factory New, Excellent, Good...).

Is it better to make 1 table with the 8 conditions in it (ConditionID, ConditionValue) and make a relationship between the Condition table and the 2 other tables or simply incorporate the Condition Values in a "pull down" on a form?

Would the answer be different if there are more than 3 similar situations? That is, assume I'm tracking Condition, Status & Finish for entries in Weapon, Accessory and RangeEquipment tables, for example?

What is a "reasonable cutoff" where it's more efficient to just stick the limited number of restricted values in a form as opposed to keeping a number of small tables and creating relationships between all of them and the various "larger"/main tables?

 

[FDibbins]

Not sure I understand, but have you considered a 2d table?
condition down the side, value across the top - table body contains the "value"?

 

[drmweaver2]

Not sure I understand...

Okay, try this...

2 tables (Weapon and Accesdory) each have a field named Condition
The allowable values for Condition are:
1 - Factory New
2 - Excellent
3 - Fine
4 - Very Good
5 - Good
6 - Fair
7 - Poor
8 - Bad

Should I use a combo-box pulldown referencing a separate Condition table (fields ID_Value, ConditionName) for this.... or just type in the values to the left of the dash? My problem is I'd need to know what the 8 values represent and I don't know how to do that. I think that's what I'm asking....

Would your answer change if there are more than 3 "major" tables with similar situations - that is a total of 8 combo-boxes on 3 different data entry forms referencing 6 other small, 2-field, tables for their values?
The other small tables include things like Status, Finish & Frame material, each of which has no more than 8 valid values.

Or would you just embed the values in the data entry form itself?

 

[xenou]

Whichever you prefer is fine. I think tables are easier to understand and maintain (one year from now are you going to remember that this information is embedded in a form control?).

If you want you can have one table. I forget how I organize this. I might call such a table DBTokens or DBLookups.
Then you can keep it all in one table:
DBType, DBToken, DBValue
Condition, Excellent, 1
Condition, Good, 2
Condition, Fair, 3
Status, Something, 1
Status, SomethingElse, 2
Status, YetAnother, 3


The values can be tricky. What if someday later you decide you want to put something between Good and Fair. There are no numbers left between 2 and 3. So you may want to just stick with text values. Otherwise you really have to treat the numbers as enums and not pay attention to whether 2 is greater than 3. It's just an identifier to distinguish the two. The cost of a join to get a value may be worth little to you in this case, if the only join you are using is to get the lookup populated with a description. So you can use the lookup tables to populate the controls, but store the values directly in the tables also.

If I did you separate lookup tables for each type of lookup, I'd probably prefix the tables with something just to keep them all together and separate from my other tables:
lkpCondition
lkpStatus
lkpFrameMaterial

 

[drmweaver2]

I appreciate the replies thus far.
Seems that the lookup tables are the way to go though I might wind up with about 8-10 of them with just 2 fields and maybe 8-15 values/data rows each.

Thanks for your thoughts on this.