Table normalization problem

[usman4575]

I wanted to create a field for city (for address) in my form but that could lead to multiple people having the same city which according to normalization means a new table to be created for the cities. So I created a new table with a primary key as its autonumber and created a relationship of the primary key from cities to foreign key of cities in the main table. So far so good but now my main form won't accept a record without first having entry of a new city in the cities table so i removed referential integrity. Now when I created a form based on these two tables, it adds every city that I enter a new entry in the cities table even for previously-stored city.
How can we fix things? it has puzzled me a lot and I have thought a lot like inner joins etc but nothing seems to work except that I put the city back in the main form and let it repeat itself (so breaking one rule of normalization).

[NeoPa]

Usman:
So far so good but now my main form won't accept a record without first having entry of a new city in the cities table so i removed referential integrity.

When you have a problem it's important that you fix it rather than simply make it go away. Fixing it makes it work. Making it go away simply shifts the problem elsewhere.

Let me stress first that an understanding of Database Normalisation and Table Structures is critical to working with databases. That takes more work than simply reading the words. You need to grasp the ideas themselves. If you can understand why they make sense then that's even better.

I'm afraid I'm struggling to understand what you've created from your description. Certainly one possibility, if you want to ensure any city used in an address is a valid one, is to link it to a table of valid cities that you maintain with only valid cities in. Let's refer to that as [tblCity] for now.

When you have that, and have set up referential integrity properly between any tables that use cities in their addresses, you will use a One-to-Many link between these tables and you should find that any time you add a city to your address it will only allow one from the list of valid cities.

[usman4575]

My apologies if i haven't put things the right way; i'm trying to fix things my way but sometimes i just can't figure them out and then turn to internet or other resources because i truly want to learn my way so that i have a strong grasp of the database.
So let me put this again, i don't have a complete list of cities but i want that whenever i enter a city name it gets stored and the next time i enter same name then that should not be repeated (data integrity so that i may not have same city with different spellings). If i create a new tblCity then the main table wants an entry in tblcity before that city can be entered in main form which would mean i need to edit city table all the time which i don't want to do.
I hope that clarifies and hope to hear something that will clarify my concepts.
thanks

[isladogs]

There is a saying...Normalise until it hurts, denormalise till it works.

Whilst you could have a separate table for each city, if you continue with this idea for handling addresses, you would also need a separate table for each district, village, street name, building name and postcode (etc). Personally, I think that is inappropriate and unhelpful for most if not all those address fields. Up to you, but I don't do this when handling addresses in any of my databases

[NeoPa]

That's a fair bit more complicated. Before you even start though, how do you plan to handle the difference between a new city that hasn't been used before and a new city that is the same as an existing city but just spelled wrong?

The way you describe it there is no way for the computer to tell the difference so you need to know what your thinking is - what your exact requirements are - before you have any hope of telling the computer how to make it happen.

I hope that helps to clarify your thinking.

NB. There are ways to handle most things but before we handle what your problem is we need to understand it clearly. For us to understand it clearly YOU need to understand it clearly first.

[NeoPa]

IslaDogs:
Whilst you could have a separate table for each city

I suspect there's some misunderstanding here. I believe Usman is trying to explain something a lot more straightforward - but is losing something in the translation. My interpretation is that when he talks of City tables he really means records.

[isladogs]

Hi @NeoPa
You may be correct but that's not my interpretation from his two posts

[usman4575]

Great! i got the idea.
My sole purpose of this thing was to ensure that i don't repeat same data with different names and now i understand that not everything needs to be "nomalized" like you can't create a new table for gender so that you avoid repeating male/female. that's just not needed and is an overkill.

[NeoPa]

IslaDogs:
Normalise until it hurts, denormalise till it works.

That makes sense. Unfortunately, many people simply don't see the first part or understand why it's there and what the normalisation part does for them.

On the other hand, if I may mix my metaphors horribly, I see my role simply as leading the horses to the water.

[isladogs]

@NeoPa wrote:
if I may mix my metaphors horribly, I see my role simply as leading the horses to the water.

...but you can't make them drink! (to quote the old proverb)

If anyone is totally baffled by the relevance of this quote, see here

[isladogs]

@usman4575
Actually, these days with many people self identifying as a whole range of other genders rather than M/F, that example isn't necessarily still completely true. See How many genders are there in 2021?

[Nauticalgent]

...but you can't make them drink! (to quote the old proverb)

However, you CAN hold it's head under water until it quits moving.

[isladogs]

Hopefully a 'nautical gent' wouldn't dream of doing so 😏

[jimatqsi]

I think something have been overlooked here. The poster reported " So far so good but now my main form won't accept a record without first having entry of a new city in the cities table so i removed referential integrity. "

What was going on was that you had no default city established or the default did not exist in the city table. Putting aside the question of whether the city table should actually be a city/state table, or even a city/state/zip table, you need to be sure that your default on new records satisfies the referential integrity. You might consider including a "Unknown City" in your city table and then set that to your default value for the city. That is what will prevent the original problem. Then you might add some logic to prevent acceptance of the record until the city is something other than "Unknown City".

Jim

[NeoPa]

Hi Jim.

I think that horse stopped drinking a while ago I'm afraid.

That said of course, it's always helpful for others that such explanations can also be found in these threads so it is worth the explanation :-)