[RESOLVED] database normalization - HELP!!

kellygocrazy

Hi,

I'm having a stupid moment and can't decide on the best way to have my database tables. I'm working on a website which is a business directory and buying/selling site for the construction industry. I'm having problems with the deciding where to store the companys address details (town & subdistrict) as the users are going to be able to search for a company by town or/and subdistrict. Currently I have town & subdistrict stored as varchar's within the business table. This table also includes company_ID, company_name, company_address, company_tel etc.

I can't decide whether to have another two tables, 'town' and 'subdistrict' with town_ID(int) and subdistrict_ID(int), and to insert these ID's into the business table instead of the full name varchars.

Would this speed up my searches on 'business by location' or would it slow it down because it would have to search multiple tables for the query. Basically int over varchar?

any help you more experienced users could give me would be a great help!!

thanks, Kelly.

Roger_Ramjet

In full 3rd normal form, you would find that it is sub-district that is an attribute of the company location and that town is an attribute of sub-district.
IE: each company is in a sub-district, and each sub-district is in a town.
This is because companies can move sub-districts but sub-districts cannot move (though they can be re-named, removed, or their boundaries re-defined).

Your table schema would therefore store sub-district id in the company table, and store sub-district and town in a town/sub-district table. A join to the town/sub-district table would yield the town for any company.

Now, just because the abstract data normalisation yields this schema does not mean that you have to implement it that way. Domain knowledge (the knowledge of how your application will use tha data) may lead to your modifying the abstract model - de-normalising some of your data - for more efficient use. This de-normalisation of the data model through domain knowledge is not only 'allowed' within the relational database methodology, it is expected. Knowing when and where to do so is the essential 'Art' of db design cos there ain't no rules, just rule-of-thumb.

In your particular case, since town is normally part of the full postal address, I would be storing it as a char field in the company table anyway. But I would still have the town/sub-district table, storing sub-district id in the company table and using a join to search for companies in a town or a sub-district.

This is because integer indexes are (almost) always more compact and faster than character indexes so a search with a join using int indexes is the way to go.

kellygocrazy

Hi Roger,

Thanks so much for your help, it's really helping to clear things up for me..
I'm just worried that by creating a seperate subdistrict table, I may be making things more complicated than they need to be. You see if I was to search for say a roofing company in eston (subdistrict) and I used a subdistrict_id in the business table, my search would have to join the services table with the business table on bus_id, and then I would have to seperatley search the subdistrict table to get the string for the subdistrict.

Also when it comes to displaying the companys details on their page I would have to get their company info from the company table and then go to the subdistrict table to get the remainder of their address. The users also have the option to search for all subdistricts within any one town...if this makes a difference.

I'm thinking that if I just store subdistrict and town in the company table it simplifies things.

i'm not sure though if this is the 'proper' way of doing things, and this is making me hesitate....what do you think?

thanks again, Kelly.

Roger_Ramjet

First thing: stop worrying about normalising data and then having to use joins. That is exactly what you are supposed to do and how relational databases work. DB engines have been optimised to perform joins efficiently.

When people worry about joins etc, it is usually because they are thinking in flat-file mode. Forget everything you know about any other type of file or database processing - relational databases have nothing in common with them.

A query that joins 2 tables on properly configure integer indexes will run faster then a query on one table using a character index. In fact, it is often better to just do a table scan rather than use a character index.

That said, I belaboured the point about domain knowledge precisely because we have to think about how the data will be used and modify the schema to make that specific processing more efficient.

Now in this case, how will the search terms be entered? What is the user interface design? I would expect that users would select from combo-boxes for town then district (use AJAX or plain javascript to make that work nicely). I would certainly not just let them type any old name into a text box.

The combo-box (drop-down list) will return the integer id for town or district which is the input into your query. That will give you the most efficient processing. No queries for mis-spelled names or non-existant districts. No results for same named towns in different states, or same named districts in different towns. Fast query processing using integer ids, and the occasional join to get all companies in all districts in a given town.

So 4 tables is the way I would go. Companies table with Foriegn Key to Districts table which has Foriegn Key to towns table, which has Foriegn Key to States table (or counties table if you are here in the UK). I don't know the scope of your data, but there are 27 towns named Dover in the usa so which Dover is the user after? Likewise there are untold towns and districts with the same or very similar names in every country in the world.

PS the other reason to use integer ids is that you don't have to update multiple records when you change the data it relates to: eg District changes name, or you miss-spelled it in the first place.

kellygocrazy

Thanks again, you're being a great help!
last point to clear up I promise...

So to pull down all of the company info onto one page (including full postal address), I would have to query all 4 tables? Have I got this right?

thanks, Kelly.

Sxooter

Just a followup. The only thing you need to pay attention to to make sure your joins are efficient is to make sure that you are joining on the same type (i.e. int to int or text to text etc...) and make sure that both ends of the keys are indexed.

kellygocrazy

Thank you both (especially Roger) for taking the time to help me out, I really appreciate it....I've taken all of your advice and should be up and running shortly...thanks!

Roger_Ramjet

Just a postscript. You should store the full postal address for the company in the companies table. The fact that you have another field that links to district and town and state should in no way affect the fact that full postal address is an attribute of a company. The other tables are for efficient grouping and queries. You don't have to worry about a few extra tables and fields these days, disk storage is so cheap.

kellygocrazy

The way I've planned it is to store the town and subdistrict of the company, in the companies table as an int which is a foreign key to the town and district tables. Is this right? Or should I store the whole address in the companies table as varchars, with an additional field for the foreign key to town/district tables?

thanks, Kelly.

Roger_Ramjet

Yes to the second scheme.

The problem with addresses is that they very so much; they can have 2, 3, 4, 5 or more lines to them anyway and may not include any reference to the district. There is no way you can efficiently query for a town in the address fields because you can never be sure which column it will be in; and if you query multiple columns for 'Dover' you'll get Dover Place/Street/Avenue as well.

Even when you try and enforce address layout through the data entry interface, you can never succeed. People will just fill in the address as they see fit, not as you would have them. Believe me, I've tried. The only way to get consistency in addresses is to do it all yourself.

You will be able to get them to select the town and district seperately as additional fields cos they'll understand the reason for this.

Here in the UK we can use the post-code for proximity searching which makes life a lot easier - people expect to enter their post-code in a specific field.

kellygocrazy

I'll go with the second scheme then, the way you've described it makes much more sense than what I was going to go with initially...thanks so much for all of your help!

Roger_Ramjet

Glad to be of help.