If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
I think I'm almost there...except for...
DK/NF is not always achievable, so I would not worry about it too much.
And as Multivalued and Join Dependencies themselves are very rare the question goes back to Functional Dependencies resolved by normalisation to BCNF, the requirement for over 95% of databases in the world. Reaching BCNF for the beginner is merely the application of a set of fairly simple rules and tests and a good helping of common sense, together with a clear understanding of the problem domain the absence of which makes "Database Design by email so very dangerous". The questioner (not necessarily referring to the current OP) is forever pulling rabbits out of the hat when one possible solution is proposed without regard to the new rabbit (as yet undisclosed to the viewing public) making the answer wrong in the light of the new information. The best and most practical advice is learn how to normalise your data and in this case it is clear there are unresolved many to many relationships here; how many depends upon the problem domain which is not being adequately explained as evidenced by over 30 emails in the last 8 days. The OP has posted more than that on other newsgroups prior to reviewing the database design which many have advised the OP to do. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "Jamie Collins" wrote in message oups.com... scubadiver wrote: Suggest you learn about normalisation and then things will just pop into place. With respect, that isn't the most constructive response. I've tried to learn about normalization as much as I can and I'm getting very bored and frustrated when I get this kind of answer. I feel the same when I see such an answer. It may make you feel better to learn this secret: not everyone posting such answers understands normalization themselves. There is a reason why people say vague things like 'properly normalized'... When was the last time someone posted to this group a 'proper' answer i.e. a complete schema in DKNF g? Jamie. -- |
#22
|
|||
|
|||
I think I'm almost there...except for...
Have you learned about the normalisation process yet?
You should really try to get to grips with it if you are to stand any chance of success. .. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... Refer to Jamie Collins response. "Craig Alexander Morrison" wrote: I will do as you suggest, but I can't see the point of a separate table for subdepartment. LEARN ABOUT NORMALISATION SO THAT YOU CAN SEE THE POINT. I must take my blood pressure medication NOW. (vbg) -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... Well, thankyou for that. "mnature" wrote: No, you're still not even close. Tables, when properly normalized, will contain information that is related to each other. You will then link the normalized tables in ways that will allow you to build queries, forms, and reports. If you do the tables wrong, then you will always have problems with everything else. You need a table that is JUST employee information. This will be: Name, address, phone, full-time, part-time, hourly wage, etc. Don't put anything into this table unless it relates directly to the employee. tbl_Employees EmployeeID (PK) EmployeeInfo EmployeeStatusInfo EmployeeRateInfo You need a table that is JUST payroll information. This will be: Link to employee being paid, link to subdepartment paying the employee, the week during which the employee worked that they are getting paid for, and whatever multiplier is needed for this particular amount (1.0 for straight pay, 1.5 for time-and-a-half, and 2.0 for double time). Don't put anything into this table unless it relates directly to the payroll. You might have multiple entries in this table for any particular employee during any particular week, because they could work for several different departments during that week, or they could work for one department but part is straight time and part is overtime. This is where you relate an employee, a subdepartment (which then relates to a department), and a payroll amount. You will end up with a whole lot of entries in this table. tbl_Payroll PayrollID (PK) EmployeeID (FK) SubDepartmentID (FK) WeekNumber PayrollAmount PayrollMultiplier You need a table that is JUST for information about your departments. Don't put anything into this table unless it relates directly to the main departments. tbl_Departments DepartmentID (PK) DepartmentInfo You need a table that is JUST for information about your subdepartments. This will include a link to the main department for a particular subdepartment. Don't put anything into this table unless it relates directly to the subdepartments. It is through the subdepartment that you get a link to a department. tbl_SubDepartments SubDepartmentID (PK) DepartmentID (FK) SubDepartmentInfo |
#23
|
|||
|
|||
I think I'm almost there...except for...
Scubadiver, I've posted comments, and I've posted questions critiquing your design in the hopes of maybe helping you. But you never responded. Other people have offered viable solutions but you had been quick to reject them in favor of your own design. So, I decided to critique your design because .. well.. I figured if you could not see the merit of a good solution, maybe you could see the lack of merit of your design. As long as we're all being sincere..... I'm one of the first ones to suggest to you that maybe you need more *dose* on the theory side. Honestly, the reason I came to this conclusion is your quick discarding of and rejecting viable solutions that had been offered (i.e. by Tim Ferguson, mnature). From the progression of your threads, I think you know enough about normalization to recognize that you have a problem, but you do not know enough about it to recognize a viable solution if you see one (i.e. solutions offered by other poster). How about starting over. Forget your design just for a while and give this a try. If this fails, you can always go back to your design. Developing a database app is really just modelling a real life process(es) into a database software product. Relational databases in particular make use of concepts of entities and relationships. Entities in your case are Employee, Department, Subdepartment, HoursWorked. Relationships describe the, well.... relationships between those entities. For example, if I'm an employee,... what is a department? Well.. I probably work for one. Or it is possible that noone really works for a Department, everyone works for a Subdepartment. So each business has its own rules. As you would expect, there is also a relationship between Department and Subdepartment. A department may have four Subdepts i.e. Operations-East, Operations-West, Operations-North, etc. The point is all the above are necessary, the entities and the relationships between the entities have to be understood and defined. This is the only way to successfully model the real world into your a relational database. You made a comment that you didnt understand why Subdepartment needs to be separate from Department. Well.. because they are two different entities, and relational database design would implement them in two tables. I hope this example can help you see that maybe a little more reading on relational database design (i.e. of which normalization is a part) would be helpful to you. HTH, Immanuel Sibero "scubadiver" wrote in message ... Well, thank you for that Jamie, I like your sincerity (I hope you are) and you are right. I get the feeling people on this board who like to consider themselves as experts aren't particularly helpful. Reading web pages don't particularly help when it comes to understanding table relations. "Jamie Collins" wrote: scubadiver wrote: Suggest you learn about normalisation and then things will just pop into place. With respect, that isn't the most constructive response. I've tried to learn about normalization as much as I can and I'm getting very bored and frustrated when I get this kind of answer. I feel the same when I see such an answer. It may make you feel better to learn this secret: not everyone posting such answers understands normalization themselves. There is a reason why people say vague things like 'properly normalized'... When was the last time someone posted to this group a 'proper' answer i.e. a complete schema in DKNF g? Jamie. -- |
#24
|
|||
|
|||
I think I'm almost there...except for...
Just some more questions. Don't consider this to be a criticism, but more of
a way of fine-tuning what you are trying to do. "Employee" EmployeeID (PK) Employee Status Rate What is a rate? Is it an hourly wage? Is it like a military rate, where you would then look up an hourly wage based on a table? Is it a job title? What is Employee field? Is that their name? If it is for the name, then you should call it EmployeeName. It is best to be specific with field names, so that you can remember what they mean six months from now when you go in to do maintenance on your database. "Payroll" Payroll (PK) Employee ID (FK) Subdepartment (FK) WeekID Amount You do not have a place to put a year. If your database lasts more than a year, you will need to know which year the week fall in. What is amount? What are the units (sounds like math class, doesn't it?)? A field name should properly describe what the data is. So, you could have TimeAmount, HoursAmount, MoneyAmount. If this is a money amount, then where are you getting that data? If you put a money amount here, then what is the rate field up in the employee table? Are you tracking hours worked, or money paid? "Subdepartment" DepartmentID SubdepartmentID (PK) You have a DepartmentID, which implies that there is a Department table somewhere. Or was this supposed to be a department name, which is entered for every subdepartment? And, though you have a subdepartmentID, there is no place for the subdepartment name. |
#25
|
|||
|
|||
I think I'm almost there...except for...
There's no need for sarcasm.
The fields I listed in my original thread are really what I want and no more. Need some re-organising maybe. Most of us that are posting here have regular jobs that we are working at. We are using a few spare minutes here and there to try and help you, and others. It can get frustrating, and sometimes we digress from helping in order to relax the atmosphere. Out where I work we deal with very intense job requirements and rules. One of those requirements is done after we have completed a document that lists what we are going to do, what could go wrong, what we'll do when things go wrong, etc. The final requirement is call Unreviewed Safety Questions. The USQ is supposed to address everything we couldn't think of in the original document. It is a real pain to fill out, because we have already racked our brains for safety problems. However, it can bring to light problems that, though unusual or very rarely encountered, can cause some very serious problems. When you come to these forums, you are talking to some people that have probably made nearly every programming mistake that can be thought of. They have progressed through the years, but nobody starts out knowing everything and doing everything right. Some of the people in this thread have been posting on this forum for several years, and probably were experts in the field long before they started posting here. It is possible to become too goal-oriented when you first start programming databases. You want to see results, you know what those results should be, and the stupid database isn't behaving correctly. You come here, and we start talking about concepts that seem to have nothing to do with your results. We talk about stepping back, and all you want to do is step forward. We talk about normalization, and you haven't a clue what that means. We tell you to add fields and tables, and all you want to do is have a form that works. You came here because you were having problems. You are going to leave here with the same problems, if you don't do something to correct them. When we tell you that you need more tables or fields, we might be seeing something that you haven't noticed, or don't have the experience to realize is a problem. |
#26
|
|||
|
|||
I think I'm almost there...except for...
Hi,
I think I have cracked it. I still need to do some tests and some slight modifications I have identified a couple of problems but I haven't changed the fields too much from what I stated originally Employee EmployeeID (PK) EmployeeName Currentwork Workstatus HourRate Calender EmployeeID (FK) YearID MonthID WeekID (PK) Department weekID (FK) Dept Subdept Costcentre Contracthrs timehalfhrs dbletimehrs In fact, you mention year and month. I have posted a question in the queries forum on how to extrapolate month and year from a week date. Then I can get rid of YearID and monthID. I knew it should have been possible with fields I have. "mnature" wrote: Just some more questions. Don't consider this to be a criticism, but more of a way of fine-tuning what you are trying to do. "Employee" EmployeeID (PK) Employee Status Rate What is a rate? Is it an hourly wage? Is it like a military rate, where you would then look up an hourly wage based on a table? Is it a job title? What is Employee field? Is that their name? If it is for the name, then you should call it EmployeeName. It is best to be specific with field names, so that you can remember what they mean six months from now when you go in to do maintenance on your database. "Payroll" Payroll (PK) Employee ID (FK) Subdepartment (FK) WeekID Amount You do not have a place to put a year. If your database lasts more than a year, you will need to know which year the week fall in. What is amount? What are the units (sounds like math class, doesn't it?)? A field name should properly describe what the data is. So, you could have TimeAmount, HoursAmount, MoneyAmount. If this is a money amount, then where are you getting that data? If you put a money amount here, then what is the rate field up in the employee table? Are you tracking hours worked, or money paid? "Subdepartment" DepartmentID SubdepartmentID (PK) You have a DepartmentID, which implies that there is a Department table somewhere. Or was this supposed to be a department name, which is entered for every subdepartment? And, though you have a subdepartmentID, there is no place for the subdepartment name. |
#27
|
|||
|
|||
I think I'm almost there...except for...
Craig Alexander Morrison wrote: The best and most practical advice is learn how to normalise your data I am of the opinion that 'Learn how to normalise your data' falls some way short of the most practical advice. Actually, I consider it a non-answer, right up there with 'Learn how to do DBMS design' and 'Learn how to program'. Here are some handy phrases to make you advice more practical: "By doing x you have violated 1NF." "You could y and your design will be in 3NF." etc. Jamie. -- |
#28
|
|||
|
|||
I think I'm almost there...except for...
No LEARN RELATIONA DATA ANALYSIS (Normalisation) is the BEST practical
advice for anyone wanting to design a Relational Database. Sure you can give them a fish or two but if they learn how to fish so much the better. Many others over 90+ messages have been providing fish; I still don't think the OP knows how to fish yet. Perhaps a brief explanation of each normal form (the rules and the tests) would help. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "Jamie Collins" wrote in message oups.com... Craig Alexander Morrison wrote: The best and most practical advice is learn how to normalise your data I am of the opinion that 'Learn how to normalise your data' falls some way short of the most practical advice. Actually, I consider it a non-answer, right up there with 'Learn how to do DBMS design' and 'Learn how to program'. Here are some handy phrases to make you advice more practical: "By doing x you have violated 1NF." "You could y and your design will be in 3NF." etc. Jamie. -- |
#29
|
|||
|
|||
I think I'm almost there...except for...
Jamie let's say I own a Ferrari F50 and I ask you what is the best way to
get from A to B in my new car and it is clear that I have not yet learned to drive. Are you going to drive me, give me directions or suggest that I should learn to drive to get the most out of my new Ferrari. My response was aimed at someone who was already out on the road with their new car and had been asking several passers by how to change gear, turn the steering wheel and brake. I know what the police would suggest to the driver. LEARN TO DRIVE WOULD BE PRACTICAL ADVICE. I am really concerned that it is not against the law to design a relational database if one does not know how to normalise. (vbg) -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "Jamie Collins" wrote in message oups.com... Craig Alexander Morrison wrote: The best and most practical advice is learn how to normalise your data I am of the opinion that 'Learn how to normalise your data' falls some way short of the most practical advice. Actually, I consider it a non-answer, right up there with 'Learn how to do DBMS design' and 'Learn how to program'. Here are some handy phrases to make you advice more practical: "By doing x you have violated 1NF." "You could y and your design will be in 3NF." etc. Jamie. -- |
#30
|
|||
|
|||
I think I'm almost there...except for...
Craig Alexander Morrison wrote: Sure you can give them a fish or two but if they learn how to fish so much the better. I can go with the fish analogy g. This guy is saying to you, the well-fed fish eater, "I'm dangling some string into my bath but I don't seem to be getting any bites" and you're shouting, "LEARN HOW TO FISH". Not practical advice. I can go with the car analogy g. This guy is saying to you, the Ferrari owner, "I'm thinking of buying a unicycle to get me from A to B" and you're shouting, "LEARN HOW TO CHOOSE A CAR". Not practical advice. Analogies: not practical advice. Perhaps a brief explanation of each normal form (the rules and the tests) would help Now you're thinking along the right lines. While you're about it, make it *practical* for the OP by relating it to their spec. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|