So far- I have to pull info from an ODBC connection (SQL server) yet that doesn't contain all the info that my boss wants for a personnel database. Nor is it normalized in my opinion. Yet I can work with some of the info that is in it. So I created a table that contains the info that doesn't exist in the ODBC table & then joined the two tables. I did this to eliminate double entry of the same info YET if I started over from scratch with my own tables, I would know that my database would be normalized & would “flow” easier in the future. I’m really having a hard time deciding what to do with this project. I want to take advantage of the existing info that already lies in the ODBC yet….
Any & all suggestions would be very much appreciated!
Application Design
Sat007
billlocke
How about this one. Given that there are three elements to an address, CITY, PROVINCE & POST CODE how should you store them
One normalization rule is that columns should depend upon the primary key of the table and nothing else, so that would imply that we need THREE tables to store the data, and a fourth table to store the combination as a series of foreign keys.
However, in the context of an ADDRESS, these three elements are directly dependent on the address and so should be stored in the SAME table.
Which way do you want to do it By the 'rules' and have separate tables for cities, priovinces and post codes That way you store an address as a street name plus foreign keys. Every time you want to retrieve an address you have to do a join across four tables....Not the most efficient way to store your data
Alternatively you could de-normalize and store the data a number of different ways. City/Province/Post Code could become a primary key so you store every possible combination and link to the street with just one foreign key.
Maybe you just don't bother, and denormalize totally and store an "address" as an "address" - which is what most people actually do!
OCardoso
I am intrigued by your response. I get the impression that sweetcoder was saying the data should be normalised but he hasn't got control of the ODBC data whereas you are saying that it shouldn't. Why not
Can you give me an example of where data cannot or should not be normalised where the developer has control of the data design
Wilton22911
Caltor you are correct about that.
smith_ranjith