Constants, one table or many?

[capsula4]

Hi!

I have a main table that has many fields like "Country", "Product", etc... which they tend to repeat.

I thought of creating one table for CONSTANTS values, like this:

COUNTRY | PRODUCT | ....
USA | ORANGE | ...
MEX | LEMON | ...
... | ... | ...

The fact is that actually USA and Orange is one entry but they actually don't have to be together necessarily.

I'm a bit newbie and all the options I think I have are:

- Using the search option selecting the info of table/row.
- Using the search option selecting one table (one for each constant).
- Adding the constants in the specific field, using the "dropdown menu" search option.

What I'm not sure if I may abuse of using too many tables if I make one for each constant, because having all of them in one table isn't logical (although works), and the last option isn't very practical so as to add more items.

Thank you for your advice!!
Luis

 

[boblarson]

Actually, you would have a lookup table for countries and you would assign the ID NUMBER, not the name. The same goes with product. It should be the ID NUMBER of the item stored and not the product name.

 

[capsula4]

Thank you bob!!

So if I understood okay, I should have a table for each "constant" like this:

[ID COUNTRY] | [COUNTRY]
--------------+-------------
1 | ...
2 | ...
3 | ...


Although I'm not really sure why should I use an ID and not just the country name, since registries wont repeat if i use only country.

 

[boblarson]

The tables would be like this

Table - tlkpCountry
CountryID - Autonumber (PK)
CountryName - Text

Table - tblProducts
ProductID - Autonumber (PK)
ProductName - Text


Table - tblMain
MainID - Autonumber (PK)
CountryID - Long Integer (FK)
ProductID - Long Integer (FK)


that is a simplistic view of it. You might actually need something else, including more tables depending on what you are trying to store.

To assign each, on a form you can have two combo boxes that each has, as its rowsource, the two fields from its respective table.

To see what I mean check out my sample here:
http://downloads.btabdevelopment.com/Samples/combos/ListboxComboSample.zip

The sample is only one combo but the concept with two is the same.

Now, from reading things you might actually have a situation which, as I said, might require more tables and you might have need of a subform or so to capture any one-to-many, or many-to-many relationships you might have.

 

[capsula4]

Thanks Bob you tips and info really helped me!!!

 

[gemma-the-husky]

if you havent already discerned this, the reason you want to store a reference number, rather than the actual value, nis for ease of updating

if you store country England for Index Entry 1, and then need to modify it then either

a) you just change the Text England to eg United Kingdom, once in the lookup table. Every other table gets that value by referencing the lookup index 1.

b) conversely if you actually store the value England thorughout the database, then changing this value to United Kingdom is decidedly non-trivial. Yes Access will probably do it without problems, but its much easier not to have to do it. Also, it is more efficeint for access to store the index value 1, then to store the longer text value.

 

[capsula4]

Thank you emma!! I actually thought about the second point tho not about the first one!!!

 

[boblarson]

Glad things seem to be falling into place.