Re: Table is multiple columns together as a unit

Post #119,608 by Nightowl 9/30/03 6:38:00 PM Reply	Re: Table is multiple columns together as a unit Ah ok, so my entire Database on Critters appears to be one large table. Maybe I could make a copy of it and try working with that then? Is that a good idea? All I ever do with it is add data and search for info and print on occasion. I need to know how to generate reports, merge, import and export.. Just not sure what information I would import or export into it. Thanks. Nightowl >8# "I learned to be the door, instead of the mat!" "illegitimi nil carborundum" Comment by Nightowl
Post #119,612 by ChrisR 9/30/03 6:41:26 PM Reply	Third normal form Designing database tables requires somewhat an understanding of Third Normal Form. But since most of the Access databases I've inherited over the ages aren't particularly normalized, that shouldn't deter you. :-)
Post #119,617 by Nightowl 9/30/03 6:47:30 PM Reply	Re: Third normal form Designing database tables requires somewhat an understanding of Third Normal Form. But since most of the Access databases I've inherited over the ages aren't particularly normalized, that shouldn't deter you. :-) Great, cause I don't know what that is. I was really good at Access in 3.11 but I ran into problems getting an understanding of it in Office '97. However, I'm a determined owl and I eventually got my Win 3.11 data base converted to '97 and worked with it there. Come to think of it... I don't remember actually "creating" one in '97, just transferring this one. Maybe I ought to start with actually trying to create one from scratch, like you suggested. I appreciate all the help, even if I don't get this job, I have always wanted a better understanding of this program. Nightowl >8# "I learned to be the door, instead of the mat!" "illegitimi nil carborundum" Comment by Nightowl
Post #119,633 by ChrisR 9/30/03 7:48:49 PM 9/30/03 8:05:35 PM Reply	1st Normal Form Starting from the basics, a Table is said to be in First Normal Form if the attributes are non-repeating. Taking the example of Critters, let's design a table that's not normalized. Let's say for each entry in the table, we have a list of up to three countries in which the critter is found: \nCREATE TABLE critter(\nname VARCHAR(80) NOT NULL,\ndescription VARCHAR(255) NOT NULL,\ncountry1 CHAR(3) NULL,\ncountry2 CHAR(3) NULL,\ncountry3 CHAR(3) NULL)\nINSERT INTO critter VALUES('Panda', 'Bear', 'PRC', 'China', NULL, NULL)\nINSERT INTO critter VALUES('Housefly', 'Insect', 'USA', 'MEX', 'CAN')\n The first problem you encounter is that some animals only can be found in one place. Since you are reserving space for 3 countries, that means that any animal that can only be found in one country has two unused cells. It also means that if you want to find the animals for any particular country, you have to look in 3 seperate columns. The second problem you have is that you've put a maximum limit on the number of countries that an animal can be found. The knee jerk reaction is to then add country4, country5,....countryn, until you've safely reserved enuf columns to cover what you think will be the maximum ever required (which only compounds the first problem). The rule of thumb is that eventually you will stumble upon some critter that can be found in every country on the planet (the common housefly would be a good example). To solve the problem, you normalize the table(s) so that any particular attribute is not repeated. So you might be more creative and have: \nCREATE TABLE critter(\nname VARCHAR(80) NOT NULL,\ndescription VARCHAR(255) NOT NULL,\ncountry CHAR(3) NULL)\nINSERT INTO critter VALUES('Panda', 'Bear', 'PRC')\nINSERT INTO critter VALUES('Housefly', 'Insect', 'USA')\nINSERT INTO critter VALUES('Housefly', 'Insect', 'MEX')\nINSERT INTO critter VALUES('Housefly', 'Insect', 'CAN')\n Not a particularly elegant solution, but you have not repeated the attributes along the columnar lines. Of course, now you have a different problem in that you have repeating data along the rows (the description of 'Insect' for the 'Housefly' is repeated 3 times). So you've really only changed the repeating of attributes from one dimesion (columnar) to another (row based). But that is the subject for 2nd Normal Form. The First Normal Form has to do with eliminating redundant columns (attributes). Edited by ChrisR Sept. 30, 2003, 07:54:34 PM EDT Edited by ChrisR Sept. 30, 2003, 08:05:35 PM EDT Expand All History
Post #119,663 by Nightowl 9/30/03 11:13:56 PM Reply	Re: 1st Normal Form Thanks, that helped some Chris. John explained that the reason my Critter database works as one huge table is because all the information depends on the critter name (name not type) directly. So each time I name a critter (which is the primary key), the information in the surrounding columns depends on the name. And there are no repeated column headings. He explained that if we were say, to add address as a heading, then it would be dependent upon the location we bought the stuffed critter, (i.e. Hallmark, Nagels, etc). and that would be an example of a separate table with repeating information. Location is already a heading, but not EXACT location, just name of store. But since there are many Hallmarks, for example, he said I could name each one with a number also, Hallmark, Vans 1, and Hallmark Vans 2, and etc. I'm starting to get the concept... but since I worked with only this one, and it's defined as a flat file database, I hadn't done much else. John said it is an example of a flat file database however, that doesn't need to be changed, which is why it works like it does. Nightowl >8# "I learned to be the door, instead of the mat!" "illegitimi nil carborundum" Comment by Nightowl

Welcome to IWETHEY!