Normalization question

[Roger Alsing 0]

Hi , i have a small normalization problem. imagine this: table User table City table Country each user can be related to a city , and each city can be related to a country (NOTE: my real problem does not invlove neither users or regions this is just a exampel) now to the problem. an User MUST always have a country , but he may or may not have a city assigned (incase the registration is incomplete... again , note the real world problem invloves products and different categories) it might be easy to say "why do you want to use something that hasnt been registered 100%" well.... i have no idea , this is just the way they work here, we sell products even if they havnt been completely registered.. so what would be the correct way to normalize such problem? should user also relate to country , even though it makes it possible to relate the user to "hungary" and relate the same user to the city "london" , which in turn relates to "england". OR should i insert an "empty" element in the city table for each country. so i can relate the user to an empty element that in turn relates to the correct country? OR is there any other "correct" way of doing this???

[slvrscremr]

Does the database platform you are using support stored procedures? Darien C#, VB.NET, Oracle, Sq(uirre)l Server "I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.

[Roger Alsing 0]

its ms sql . but what has storedprocedures to do with table normalization rules? //Roger

[slvrscremr]

Well.... I was brainstorming and trying to think about different ways you could ensure the integrity of the data. i.e. Allowing London, U.K. and disallowing Los Angeles, Nigeria... Darien "I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.

[slvrscremr]

Speaking strictly from a normalization perspective, since any given user can only have one city or state, I would probably have a CityId and a CountryId element in the users table. I would make the countryId NOTNULLABLE and the cityId NULLABLE. I would then have those two id columns referenced to two lookup tables ( tblCountryLkp, tblCityLkp ? ) that would contain valid entries for their specific entities. A stored procedure could then be used to reference another table ( tblValidCountryCities ? ) that would contain all valid combinations of cities and countries. Any insertion would be validated against this table and a non-conforming combination would result in an error. Hope this helps, Darien "I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.

[Steven Campbell]

Consider removing city from your user table, and placing it in an associated user_city table (user_city_id, user_id, city_id). This makes it clear that the city is optional information that is not (reliably) part of the core user information.

---

my blog