When to use text vs. Number for field

[RainLover]

My data is separated into different tables, with relationships between them, using PKs and FKs. Each level has it's own table.

For example:
tblcat1
Cat 1 ID PK
Cat 1 ID
Cat 1 Description

tblCat2
Cat 2 ID PK
Cat 2 ID
Cat 2 Description
Cat 1 ID FK

tblCat3
Cat 3 ID PK
Cat 3 ID
Cat 3 Description
Cat 1 ID FK (I realize this probably didn't need to be added in here)
Cat 2 ID FK

I think you have a good approach. Your original question was about data type and I believe that this should be number.

With your tables above, I would remove all the Foreign Keys and create a Fourth Table with the three FKs in separate fields.

Then you just need to format and concatenate the result. Do you have a problem with that. If so I can post the string.

 

[bigalpha]

Sorry for the delay.

Why would you put all of the FKs in one table, instead of the way they are now? Does it lend a benefit? I'd appreciate it if you could post the string.

Thanks!

 

[RainLover]

The Fourth table would contain additional information. That is for you to work out what that information that is. Within that table you would have three fields that hold the foreign keys to the other 3 tables. This will allow you to build any combination that you want. I did not mean to suggest to put every possible combination into a fourth table.

Attached is a sample Database with some queries that demonstrate the Formating of the three fields.

 

[jdraw]

I think ChrisO was asking what exactly you have done and where do the pieces fit. I have a feeling you have not understood his post or may not be aware of things like
http://www.epa.gov/osw/inforesources/data/br91/na_apb-p.pdf

I could very well be way off, but it seems you may not have told us some of what you are working with - that could be useful when communicating with readers.

 

[bigalpha]

I see that I haven't adequately explained what we're doing. I've attached the three tables of data that we will be using as identification data.