Better to store text or numeric

[KenHigg]

The logic in this thread suggests if like values could exist across records then no questions ask, put it in a separate table and fk to it.

The user only wants to store "Poor - Fair - Good - VeryGood - Excellent" !?! Just keep it in the same, main table. You can use a look up table to populate a combo box to keep the data consistent but why in the world would you want to have to look up what a 1,2,3,4, or 5 is every time?? It'd be like using a separate table to store a "1" in place of "Mr" for every name. If you followed this logic you'd eventually have a main table that has nothing but fk's !?!?

A quick look into our SAP/Oracle tables proves the point I'm trying to make...

 

[gemma-the-husky]

The logic in this thread suggests if like values could exist across records then no questions ask, put it in a separate table and fk to it.

The user only wants to store "Poor - Fair - Good - VeryGood - Excellent" !?! Just keep it in the same, main table. You can use a look up table to populate a combo box to keep the data consistent but why in the world would you want to have to look up what a 1,2,3,4, or 5 is every time?? It'd be like using a separate table to store a "1" in place of "Mr" for every name. If you followed this logic you'd eventually have a main table that has nothing but fk's !?!?

A quick look into our SAP/Oracle tables proves the point I'm trying to make...

ken

what if he changes "Poor - Fair - Good - VeryGood - Excellent" at some point to also want to store

"N/A", "Super"

or if he wants to change "Good" to say "Satisfactory". Surely, thats the point.


with regard to "personal styles" - then surely you "would" have a lookup table to select from available styles, and add extra ones.

Mr
Mrs
Miss
Ms
Prof
Dr
Rev

etc

it limits the selections to an agreed list. And you may also want to store additional characteristics against some or all of these values.

Occasionally, I break my rule, and use a value list for a list box, combo box, but it isnt very often at all

 

[SOS]

ken

what if he changes "Poor - Fair - Good - VeryGood - Excellent" at some point to also want to store

"N/A", "Super"

or if he wants to change "Good" to say "Satisfactory". Surely, thats the point.


with regard to "personal styles" - then surely you "would" have a lookup table to select from available styles

Mr
Mrs
Miss
Ms
Prof
Dr
Rev

etc

it limits the selections to an agreed list. And you may also want to store additional characteristics against some or all of these values.

Occasionally, I use a value list for a list box, combo box, but it isnt very often.

Hear, Hear! I concur with that (for what it is worth - and that's not much )

 

[KenHigg]

ken

what if he changes "Poor - Fair - Good - VeryGood - Excellent" at some point to also want to store

"N/A", "Super"

or if he wants to change "Good" to say "Satisfactory". Surely, thats the point.


with regard to "personal styles" - then surely you "would" have a lookup table to select from available styles, and add extra ones.

Mr
Mrs
Miss
Ms
Prof
Dr
Rev

etc

it limits the selections to an agreed list. And you may also want to store additional characteristics against some or all of these values.

Occasionally, I break my rule, and use a value list for a list box, combo box, but it isnt very often at all

If you want to add/delete one then add/delete it to the table that controls the combo box. If you want to change the x's to a y's then run an update query.

I'm not sure what your point was with the Mr. thing...

 

[rainman89]

Glad to see that I'm not at a total loss for suggesting this... couldn't think of a good reason at the time, but these are all very good reasons!

 

[Sess]

woah. I did not think I would upset the apple cart witht at question. Thank you for your replies.

Right now there are 12 fields that can take the poor to excellent criteria or 1 to 5, but I was told there could be more added in the near future. There would be thousands of records so it could get rather big and the machines are older technology and also I have to use Access2003. Just do not want to bog down the machines with the application.

I am not going to sum the fields so also if it is set as a single position text or a numeric would there be a difference?

What does PK/FK refer to please?

 

[KenHigg]

You didn't upset anything. I think we all respect each others opinions when it comes to db's. Read through what we've all posted and pick what you think will work for you!

 

[Banana]

woah. I did not think I would upset the apple cart witht at question. Thank you for your replies.

Don't worry about that - I think it was quite good discussion and after all, you're entitled to full spectrum of opinions, I would think.

Right now there are 12 fields that can take the poor to excellent criteria or 1 to 5, but I was told there could be more added in the near future. There would be thousands of records so it could get rather big and the machines are older technology and also I have to use Access2003. Just do not want to bog down the machines with the application.

If you're going to add more in future, it may be preferable to use surrogate ID so you only need to modify one little lookup table and not the big table of actual records.

I am not going to sum the fields so also if it is set as a single position text or a numeric would there be a difference?

You may not sum the fields, but you may count it or order on it, so in my humble opinion, smaller = better.

What does PK/FK refer to please?

Primary key
Foreign key

 

[KenHigg]

pk/fk is primary key and foreign key.

 

[KenHigg]

What do you mean by 'there are 12 fields that can take the poor to excellent criteria or 1 to 5'? 12 fields in one table and each one of them will get a rating?

 

[Sess]

I did not think you were battling it out, just I expected perhaps a post or two to suggest the best directions and not a big discussion. Anyhow I did learn a lot from these posts so I thank you all for your time so far.

Yes there are 12 fields at the moment in a single table which will have the rating.
I think I will be setting up a second table as suggested to have the 1 through 5 ranking relate to the verbage.

Thank you.