should all data be normalized

[tagteam]

If I have a table that has repeated data but it doesn't need any additional information associated with it should I still make a table and use the ID in the main table?
I understand why you would normalize customer. If you did you could then add address, contact info etc and still only have one main value in the table. However, if I have something like contents that will be repeated several times but doesn't need any other fields associated with it, it seems easier for searching to just leave that field as its text value.

My thoughts are:
It is easier to add a value to a table than a value list for users who are not experienced with access.
If the list has 30+ items it can be more difficult to manage than a table
HOWEVER,


Searching is more difficult if you have a number instead of the value in the field. You can't do the simple finds and filters.

Basically I am asking:
1. Should i use a select list instead of creating a table if I want the data to be consistent but the field in question is a single text string and doesnt have any data related to it?

[June7]

In my view, it is a balancing act between normalization and ease of data entry/output. But I don't think your question is really about normalization, it is more about what should be saved as value in field - number or descriptor and what should be the source for those values.

If you want to standardize values but make it easy for addition of new values, use a table as source for these entries. Whether you save the actual text or a numeric key is your preference. Indexing is supposed to be faster with numbers, especially in extremely large dbs but then must deal with lookup aliases in comboboxes and join tables in queries to retrieve the associated descriptor.

[tagteam]

As I was googling this I came across a guy who was saying a similar thing as you ( I think) If the field doenst have too many values just create the table relationship on the text field itself. So if I am understanding correctly this would mean you have the field in the main table be a text field and the field in the lookup table be text and you join on those. The other way being you would have something like a number field in the main table linking to an autonumber field in the lookup table and then you would have to do the whole combo box thing when you look stuff up.

If that is the case, when you create your query to base the form on do you then choose the value in the main table or the lookup table? Since they would be the same i would imagine you would use the main table but when users were trying to enter a new record you would use the lookup table.

[June7]

Maybe the number of value options has a bearing on this question, not sure, because it isn't the source table that needs to be sorted/filtered, it is the data table the values are saved in.

And joining the tables in queries on the text desriptor would only be necessary if there is other data in the source table you need to view.

A combobox is relevant in either case, it's just whether or not you want a multi-column with lookup alias. Regardless, the source table would be used to build the combobox list.

[tagteam]

Yes, i built an example and it is kind of pointless to create a lookup table that only has one text field and use that for the join. It is basically just a way to create a value list but do it in a table instead.

I am thinking just to stick with the tried and tested ID number in parent table and link it to autonumber in child table. My main concern was that on the form it would not be able to find / filter on the name but would require the ID in the child table but after running some tests it seems that if the form is based on a query and they value pulled is the text value from the child table you can still find / filter based on the text value.

Would you recommend that anything with more than 5 - 10 values OR anything that you might need to add values to frequently should be in a table and use a lookup and anything that has around 5 values and doesnt change much can just be a value in the table and use a value list to limit entry to your desired values.

Thanks for any help, i am wanting to make sure I build this in the best way now because it can be hard to change it later.

[June7]

A little nit-picking here. The source table is not a 'child' table. Dependent child tables cannot have record without related parent record, otherwise results in orphans. An example would be tblOrders and tblOrderDetails. Source (lookup) tables can have records that are never associated with other records.

That is a reasonable 'rule of thumb'. Key phrase - 'doesn't change much' - some would say if there is ANY chance of changes, use a table. Are you building this for your use, will it have to survive your tenure with company and someone else must take your place and manage the db?

[tagteam]

It will have to survive tenure with the company. I built their database 17 years ago in approach and didn't know what I was doing and everything is in one table. I am now back working with them again after having been gone 12 years and they are on the same database. So i am trying to fine tune it as well as add several reporting features.

I want to do a good job normalizing but when you have so many fields with just a number then it can be harder for a regular user to create a query if they dont understand the relationship issue.

[June7]

Yes, one must understand relational database principles and the design of specific db. However, don't think the learning curve for Access is as steep as with some other db apps.

Just don't set Lookups in table if alias is involved. This will further confuse a newbie.