473,288 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Table normalization problem

5 Nibble
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).
May 13 '21 #1
14 2631
NeoPa
32,554 Expert Mod 16PB
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.
May 13 '21 #2
usman4575
5 Nibble
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
May 13 '21 #3
isladogs
454 Expert Mod 256MB
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
May 13 '21 #4
NeoPa
32,554 Expert Mod 16PB
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.
May 13 '21 #5
NeoPa
32,554 Expert Mod 16PB
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.
May 13 '21 #6
isladogs
454 Expert Mod 256MB
Hi @NeoPa
You may be correct but that's not my interpretation from his two posts
May 13 '21 #7
usman4575
5 Nibble
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.
May 13 '21 #8
NeoPa
32,554 Expert Mod 16PB
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.
May 13 '21 #9
isladogs
454 Expert Mod 256MB
@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
May 13 '21 #10
isladogs
454 Expert Mod 256MB
@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?
May 13 '21 #11
Nauticalgent
100 64KB
...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.
May 14 '21 #12
isladogs
454 Expert Mod 256MB
Hopefully a 'nautical gent' wouldn't dream of doing so 😏
May 14 '21 #13
jimatqsi
1,271 Expert 1GB
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
May 14 '21 #14
NeoPa
32,554 Expert Mod 16PB
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 :-)
May 14 '21 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Muzamil | last post by:
hello I've a denormalized table PRODUCTS with following fields: ProductNo , OrderNo , SerialNo , OrderDate , PromiseDate ,
5
by: Josh Renaud | last post by:
I'm still trying to solve a problem I have experienced in Safari. This is my third post on the subject. I'm hoping someone can shed some light. The problem is that, in Safari, a table with no...
0
by: Thomas Scheiderich | last post by:
I have a table that has 3 image slices. The middle slice changes size based on size of browser window. This works great. My client wants the date to show over the image on the right. So what I...
1
by: murray | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment...
0
by: OctoTools | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment...
7
by: Kamal | last post by:
Hello all, I have a very simple html table with collapsible rows and sorting capabilities. The collapsible row is hidden with css rule (display:none). When one clicks in the left of the...
0
by: selimzairi | last post by:
hi to all would any body help please on this cross table report problem yrs header -for years grouping mnt header -for monthes grouping state_id -for states columns 1->30 is for branches id;...
0
by: John Roberts | last post by:
Hi guys, My database is accessed by several users. When the user opens my report it first makes a table of temporary data from one query, then uses another query on that table to populate my...
4
by: Dani | last post by:
Hi, I'm developing and HR database with Access 2010. I've hit a snag with designing a Performance Evaluation table. I have various lookup tables for: EmployeeID SupervisorID...
1
by: sman | last post by:
i wish to now know how to normalize a table which have duplicate names and number repeated severally and i tried normalizing but could not
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.