Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can a table be made to accept only like 10 items? 1
- Thread starter Tasuki
- Start date
- Thread starter
- #3
Hmm, but what if each item had something I needed to search by?
For example, each item can be bought by different programs, so for a report I can query by "Program" to list all items purchased by that program.
I don't think it's possible to do a query like that if all the item fields are in the purchase order form table... or can it?
T
For example, each item can be bought by different programs, so for a report I can query by "Program" to list all items purchased by that program.
I don't think it's possible to do a query like that if all the item fields are in the purchase order form table... or can it?
T
-
1
- #5
RiverGuy,
PLEASE read some about data normalization before making suggestions on table structure. Your suggestion for ten repeating fields is contrary to one of the three cardinal rules of database design, and will lead to many problems.
Your next suggestion, about the ten textboxes is a lot more on target, though would require unbound controls and careful programming.
The way this should be dealt with, Tasuki, is through forms and/or code. Access does not provide for this sort of validation at the table level. In a well designed database application, the users will not have the ability to touch the tables or queries directly, which allows you to control what they do to the data to a much greater degree.
Probably the best way to handle this situation is to have a main form and a subform. When the main form goes to a new record, and when a new record is added to the subform, run code to check how many records there are on the subform. If it's ten, change the AllowAdditions property to false.
For both of you, I have an article called Fundamentals of Relational Database Design on my website. It was written by Paul Litwin, a guru of Access development issues, and it it explains normalization quite well.
Hope this helps.
Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
PLEASE read some about data normalization before making suggestions on table structure. Your suggestion for ten repeating fields is contrary to one of the three cardinal rules of database design, and will lead to many problems.
Your next suggestion, about the ten textboxes is a lot more on target, though would require unbound controls and careful programming.
The way this should be dealt with, Tasuki, is through forms and/or code. Access does not provide for this sort of validation at the table level. In a well designed database application, the users will not have the ability to touch the tables or queries directly, which allows you to control what they do to the data to a much greater degree.
Probably the best way to handle this situation is to have a main form and a subform. When the main form goes to a new record, and when a new record is added to the subform, run code to check how many records there are on the subform. If it's ten, change the AllowAdditions property to false.
For both of you, I have an article called Fundamentals of Relational Database Design on my website. It was written by Paul Litwin, a guru of Access development issues, and it it explains normalization quite well.
Hope this helps.
Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Thanks for the flame Jeremy, that was pretty cool of you. Anyways, data does not always need to be normalized. Quite often, too much normalization is a bad thing. Can you imagine keeping a zip code table and making a customer pick one that exists in your table, and your table not being updated?
And if you don't want to use a datagrid on the subform, you can always use a counter and increment it when it the record is saved, when 10 is reached, thats it. When a new order record is created, reset the counter.
And if you don't want to use a datagrid on the subform, you can always use a counter and increment it when it the record is saved, when 10 is reached, thats it. When a new order record is created, reset the counter.
Hi!
To RiverGuy: Though Jeremy may have been able to state his case more graciously, I still must side with him in this matter. Your comment that data need not always be normalized, while not as far off base as your first suggestion to add repeating fields, must be strongly qualified. In almost all cases, 99.9999...% you get the idea, normalization should be followed and followed carefully. In fact, I can't even think of a good example where it shouldn't be followed. Even for cases where the requirements of the db seem to make it unnecessary, unnormalized data will come back and bite you eventually since requirements never remain static.
To Tasuki: Jeremy's method is good but don't forget to display a message for the user (at least) so they don't think that they locked up the computer and do something foolish like reboot. You could just have the form automatically go to a new order and allow the user to continue adding items. Of course an informational message is necessary in this scenario also.
hth
Jeff Bridgham
bridgham@purdue.edu
To RiverGuy: Though Jeremy may have been able to state his case more graciously, I still must side with him in this matter. Your comment that data need not always be normalized, while not as far off base as your first suggestion to add repeating fields, must be strongly qualified. In almost all cases, 99.9999...% you get the idea, normalization should be followed and followed carefully. In fact, I can't even think of a good example where it shouldn't be followed. Even for cases where the requirements of the db seem to make it unnecessary, unnormalized data will come back and bite you eventually since requirements never remain static.
To Tasuki: Jeremy's method is good but don't forget to display a message for the user (at least) so they don't think that they locked up the computer and do something foolish like reboot. You could just have the form automatically go to a new order and allow the user to continue adding items. Of course an informational message is necessary in this scenario also.
hth
Jeff Bridgham
bridgham@purdue.edu
Is normalization ALWAYS needed? In the higher forms anyways? What about a survey? When the db designer does not know all possible answer/categories.
Consider a field called "First Car." To be normalized, the database would have to have a make, model, year, and trim level tabel. Can you imagine the research to populate those tables with every car ever made for a simple survey?
Consider a field called "First Car." To be normalized, the database would have to have a make, model, year, and trim level tabel. Can you imagine the research to populate those tables with every car ever made for a simple survey?
Jeff,
One common place to denormalize is a sales database, where you want to store calculated totals for invoices, because prices of items, and even tax rates may change over time. But yes, the rules are to be broken only after serious thought.
And your suggestion of a message to the user is spot on. Thanks for that.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
One common place to denormalize is a sales database, where you want to store calculated totals for invoices, because prices of items, and even tax rates may change over time. But yes, the rules are to be broken only after serious thought.
And your suggestion of a message to the user is spot on. Thanks for that.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Hi again!
Jeremy - I usually use an archive database for the situation you describe, but will admit that some users refuse the absorb the costs of doing somthing correctly so the developer is stuck with denormalized data. I try mightly to convince my users to keep at least the production tables normalized to allow for efficient data entry and validation and the ability to query the data any way they want to. Maybe we should take the normalization discussion to the group here in general to see what their thoughts are as to when the rules should be broken.
RiverGuy - I'm sorry, but I still disagree. In a survey database, normalization is particularly critical. The whole function of the Db is to analyze the data acquired and you are sure to run into problems if the data isn't normalized. In the instance you describe the data is not denormalized if you have fields called MakeOfFirstCar, ModelOfFirstCar, YearOfFirstCar etc. If the survey is to find out what different cars the respondees have owned then you will need to put more thought into it. Probably ending up with the fields Make, Model, Year, etc and adding a field for which car it is first, second, current. Anything about a car that isn't variable for the make/model/year, say it only came with one type of headlight (assuming that was important to the survey) then that should be added in a separate table later by the people administering the survey. My point is that, if there is any way to do what you want and keep the data normalized, do it that way!
hth
Jeff Bridgham
bridgham@purdue.edu
Jeremy - I usually use an archive database for the situation you describe, but will admit that some users refuse the absorb the costs of doing somthing correctly so the developer is stuck with denormalized data. I try mightly to convince my users to keep at least the production tables normalized to allow for efficient data entry and validation and the ability to query the data any way they want to. Maybe we should take the normalization discussion to the group here in general to see what their thoughts are as to when the rules should be broken.
RiverGuy - I'm sorry, but I still disagree. In a survey database, normalization is particularly critical. The whole function of the Db is to analyze the data acquired and you are sure to run into problems if the data isn't normalized. In the instance you describe the data is not denormalized if you have fields called MakeOfFirstCar, ModelOfFirstCar, YearOfFirstCar etc. If the survey is to find out what different cars the respondees have owned then you will need to put more thought into it. Probably ending up with the fields Make, Model, Year, etc and adding a field for which car it is first, second, current. Anything about a car that isn't variable for the make/model/year, say it only came with one type of headlight (assuming that was important to the survey) then that should be added in a separate table later by the people administering the survey. My point is that, if there is any way to do what you want and keep the data normalized, do it that way!
hth
Jeff Bridgham
bridgham@purdue.edu
Yeah Jebry, you can still have all of those fields in the table. There is no way you would have a lookup table for every individual criteria. Some cars in the 60s had around 20 different engine options. Would you want to do two weeks of research to make Model-Engine intersection tables? What if someone forgot that less than 100 Ford Fairlanes in 1964 came with 427s? Were they hi-rise/low-rise, 4 bbl, 2x4bbl, sohc? What if Ford doesn't even know (truthfully, car experts do not always know what was produced and when, especially in limited edition).
Jermemy did have a good point though. Data attributes constantly change. You quit your job, someone changes the description of an object you've made. Using a lookup table would state your object had such and such characteristics, was responsible for this and that. When thats not the case. Thats why in your bulk records table you go ahead and write out the description and not just the object's ID.
Full normalization isn't appropriate anywhere near 99% of the time.
As far as efficient data entry, too much normalization will decrease efficiency. Evaluating every field's entry against a lookup table is a lot less efficient than allowing some of the fields to accept any data.
Jermemy did have a good point though. Data attributes constantly change. You quit your job, someone changes the description of an object you've made. Using a lookup table would state your object had such and such characteristics, was responsible for this and that. When thats not the case. Thats why in your bulk records table you go ahead and write out the description and not just the object's ID.
Full normalization isn't appropriate anywhere near 99% of the time.
As far as efficient data entry, too much normalization will decrease efficiency. Evaluating every field's entry against a lookup table is a lot less efficient than allowing some of the fields to accept any data.
Hi!
Well, that is an interesting perspective. Normalizing data has nothing to do with lookup tables, that is data validation. I guess Jeremy had the right idea at first, go look up his article so you understand data normalization.
hth!!
Jeff Bridgham
bridgham@purdue.edu
Well, that is an interesting perspective. Normalizing data has nothing to do with lookup tables, that is data validation. I guess Jeremy had the right idea at first, go look up his article so you understand data normalization.
hth!!
Jeff Bridgham
bridgham@purdue.edu
Ummm, ok. Well if you have a column called "Car" and a column called "Color", and one record that contains "VIN#1" and "Green", and you delete that one record, then you put the VIN# back in, do you remember that the car is green?
So basically normalizing data accepts invalid data, according to you?
So basically normalizing data accepts invalid data, according to you?
Hi!
Being an optimist by nature, I still have hopes that you will eventually understand this, therefore I answer! Since VINs are unique to a specific car the fields as you describe them are normalized. If that car was green and the user put in red, that would be incorrect data. Now catch this part, normalized data, like any other data, can be wrong! That is what data validation is for, and therefore that is what lookup tables are for. Obviously, color was not a good choice for your example since cars have their color changed all of the time and the lookup table would be as likely to be wrong as what the user inputs, but I do understand your point. Validation can be and often is painful and difficult. Sometimes, as you point out, it is not worth doing. That is true if the data is normalized or if it isn't, it is a completely different issue. If you want to do a survey about cars and their colors, I would recommend normalizing the data and trust the responders to get the color right!
hth
Jeff Bridgham
bridgham@purdue.edu
Being an optimist by nature, I still have hopes that you will eventually understand this, therefore I answer! Since VINs are unique to a specific car the fields as you describe them are normalized. If that car was green and the user put in red, that would be incorrect data. Now catch this part, normalized data, like any other data, can be wrong! That is what data validation is for, and therefore that is what lookup tables are for. Obviously, color was not a good choice for your example since cars have their color changed all of the time and the lookup table would be as likely to be wrong as what the user inputs, but I do understand your point. Validation can be and often is painful and difficult. Sometimes, as you point out, it is not worth doing. That is true if the data is normalized or if it isn't, it is a completely different issue. If you want to do a survey about cars and their colors, I would recommend normalizing the data and trust the responders to get the color right!
hth
Jeff Bridgham
bridgham@purdue.edu
Jeff,
It is a fun discussion, and realize that none of this is meant to be flames and hope its true on your part too.
I was not describing the scenario in the context of a DMV database. But if you were a car collecter, and ID'd your cars on VIN. Do you see why if you happened to delete your car, you may want to only go back in and input a VIN instead all of your other records? The VIN would be kept in your VIN table, possibly with some other attributes.
To input a new maintenance record, you would not have to go back in and re-create the car, only reference the VIN, and all the day, if you changed the oil, or whatever you're tracking.
BTW, normalization strives to prevent anomolies. Data would be anolalous if you deleted one car record, then entered it over again, and accidentally put "red".
Too bad we got off Tasuki's topic
It is a fun discussion, and realize that none of this is meant to be flames and hope its true on your part too.
I was not describing the scenario in the context of a DMV database. But if you were a car collecter, and ID'd your cars on VIN. Do you see why if you happened to delete your car, you may want to only go back in and input a VIN instead all of your other records? The VIN would be kept in your VIN table, possibly with some other attributes.
To input a new maintenance record, you would not have to go back in and re-create the car, only reference the VIN, and all the day, if you changed the oil, or whatever you're tracking.
BTW, normalization strives to prevent anomolies. Data would be anolalous if you deleted one car record, then entered it over again, and accidentally put "red".
Too bad we got off Tasuki's topic
Hi!
Yes normalization attempts to reduce anomalies. Actually, with data that is totally normalized anomalies are not possible. In the situation you describe, if the data is normalized, and there is a table for VINs and certain other data, including the color, and a record was deleted and then re-entered incorrectly. The data would be wrong, but not anomalous because, wherever the data was used, it would always say red. I grant you, it would always be wrong, but it would also always be the same. There is nothing in the world that can stop incorrect data from being put into a database, that is why validation is reserved for critical data and lookup tables are, usually, reserved for data with limited possible values.
Let me apologize if I sounded irate. I'm ready to go home!!
hth
Jeff Bridgham
bridgham@purdue.edu
Yes normalization attempts to reduce anomalies. Actually, with data that is totally normalized anomalies are not possible. In the situation you describe, if the data is normalized, and there is a table for VINs and certain other data, including the color, and a record was deleted and then re-entered incorrectly. The data would be wrong, but not anomalous because, wherever the data was used, it would always say red. I grant you, it would always be wrong, but it would also always be the same. There is nothing in the world that can stop incorrect data from being put into a database, that is why validation is reserved for critical data and lookup tables are, usually, reserved for data with limited possible values.
Let me apologize if I sounded irate. I'm ready to go home!!
hth
Jeff Bridgham
bridgham@purdue.edu
One last thing though, it wouldnt always be red. If you remembered it was green tomorrow and added a new maintenance record with green, and you entered green, then it would be anomolous. Oh well, I think we've done enough damage to Tasuki's thread.
- Thread starter
- #18
Heh heh, I've been watching the thread but have not replied because everyone's discussion is beyond me, I think I will go read that article from Jeremy's website. I've taken Jeremy's suggestion into consideration, and thanks for the replies and that most interesting debate which I didn't quite understand much of it. 
Thanks again!
T
Thanks again!
T
Tasuki,
Yeah, it's really a great paper. I've poked around a bunch on the web looking for something better than this and not found anything. Litwin manages to take a rather complex subject and present it in an extremely clear manner.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Yeah, it's really a great paper. I've poked around a bunch on the web looking for something better than this and not found anything. Litwin manages to take a rather complex subject and present it in an extremely clear manner.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
Jeff,
It's funny...I'm reading some about SQL server now, as I've got a SQL server job coming up. In MS's literature (online) they spend some time talking about the value of denormalizing for extremely large sets of data. I've never worked with anything over 100,000 rows and about 15 tables, so I've always worked hard to be fully normalized. I don't think the upcoming project will involve more than that either, but it will be interesting to start playing in a realm where it's more likely that I'll bump into performance issues created by over normalization.
But, as you have been pointing out in your posts in this thread, GIGO rules, and has nothing to do with normalization.
Jeremy
PS: You're not an undergrad student at Perdue, are you? Would be quite impressive, if so. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
It's funny...I'm reading some about SQL server now, as I've got a SQL server job coming up. In MS's literature (online) they spend some time talking about the value of denormalizing for extremely large sets of data. I've never worked with anything over 100,000 rows and about 15 tables, so I've always worked hard to be fully normalized. I don't think the upcoming project will involve more than that either, but it will be interesting to start playing in a realm where it's more likely that I'll bump into performance issues created by over normalization.
But, as you have been pointing out in your posts in this thread, GIGO rules, and has nothing to do with normalization.
Jeremy
PS: You're not an undergrad student at Perdue, are you? Would be quite impressive, if so. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question