I think I'm almost there...except for...

[Craig Alexander Morrison]

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.

 

[Craig Alexander Morrison]

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.
..

 

[Immanuel Sibero]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Jamie Collins]

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.

--

 

[Craig Alexander Morrison]

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.

 

[Craig Alexander Morrison]

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)

 

[Jamie Collins]

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.

--

 

[Craig Alexander Morrison]

The advice was practical; the analogies were to demonstrate that.

I'm not so sure that your analogies were entirely fair to the OP.

The definition of practical in your message seems to be short term and
limited.

Why would it be -impractical- for the OP to learn about normalistion?

After all the OP is not designing an insignificant hobby database but one
that has financial data and therefore financial implications for their
company, why not do it right, even if that means they cannot do it right
now?

 

[Jamie Collins]

The advice was practical; the analogies were to demonstrate that.

I again urge you to go with your earlier idea e.g. explain in which
normal form (if any) you think the OPs design is currently, which of
the normal forms he should be aiming for and - to give it the practical
element lacking thus far - what you think he needs to do to get his
design to to your recommended normal form.

Jamie.

--

 

[Craig Alexander Morrison]

I would urge you to read my earlier replies and others on the subject of
understanding the problem domain.

Why do you appear to believe it is impractical to learn about normalisation?

 

[Craig Alexander Morrison]

DK/NF is the goal, 3NF/BCNF is acceptable in most situations, but that is
accepted convention.

Do you think you have enough information from the OP to tell me the answer
to your own question?

 

[Guest]

I think I have cracked it.

Well, in a word: NO.

I still need to do some tests and some slight modifications

Won't take many tests for you to find that this just won't work. And it
isn't just slight modifications that you need.

I have identified a couple of problems but I haven't changed the fields too
much from what I stated originally

I know you haven't changed the fields too much from what you stated
originally. I'm not sure why you haven't changed them. You have been
presented with a number of different ways of normalizing your tables, in
order for your database to just simply work. It will not work in this form
that you have posted.

Employee

EmployeeID (PK)
EmployeeName
Currentwork (what does this mean?)
Workstatus
HourRate

Calender

EmployeeID (FK)
YearID (Why are you putting this in? Why not just have a simple date, and extrapolate whatever you want from that date?)
MonthID (Again, why???)
WeekID (PK) (You cannot make this a true week-of-the-year number, and also have it as the primary key. Your database fails with this particular field alone. As soon as you start putting in data, that should become obvious.)

Department (no primary key?)

weekID (FK)
Dept
Subdept
Costcentre
Contracthrs
timehalfhrs (Why are you defining the type of hours in a field name?)
dbletimehrs (You are going to put in two types of hours within one record? Plus, you don't have a reasonable foreign key to relate these hours back to any particular week or person.)

I've posted suggestions before. I will not do that here. But this will not
work, so you need to try reshuffling everything again.

 

[Craig Alexander Morrison]

I again urge you to go with your earlier idea e.g. explain in which

normal form (if any) you think the OPs design is currently, which of

Sorry should have kept this all in a single message but...

It would be difficult not to be in 1NF, I am sure you would know this.

It was your idea to delve into the user requirements and apply them to the
rules and tests of normalisation my idea was that the OP should learn the
rules and tests so that they can apply them to the user requirements
themselves. I am happy to post those rules and tests and even the preamble
to the normalisation process, however these are available from many sources
already.

 

[Guest]

I again urge you to go with your earlier idea e.g. explain in which
normal form (if any) you think the OPs design is currently, which of
the normal forms he should be aiming for and - to give it the practical
element lacking thus far - what you think he needs to do to get his
design to to your recommended normal form.

Jamie.

Jamie and Craig, I have been trying to explain things to scubadiver. I'm
not in the same class as you two on understanding database design, I'm more
of a semi-talented beginner about all of this. But I've tried to give
scubadiver some good basic advice, and he won't listen. And his database
design doesn't get any better (his latest is actually a regression). I've
enjoyed your philosophical exchange on how to guide a clueless beginner into
designing a reasonable database. I'd like to invite both of you to just jump
in and try to help this poor schlimazel, each with your own special
philosophy of how to help a beginner. Please. Pretty please.

Your combined wisdom and knowledge would be an inspiration to us all.

 

[Craig Alexander Morrison]

Well now I have been challenged by Jamie and asked nicely by you.

I will review all of the OPs threads in the last 14 days and try to document
what I believe they are trying to achieve and what the problems may be.

I will add this caveat - that Database Design by Email is a very dangerous
exercise but I will detail any assumptions I make that would affect the
result.

In other words the answer maybe wrong for the OP but right for the
assumptions that have been made about the OP's user requirements and problem
domain.

These assumptions are normally questions you would be asking the end user
about how they use and would want to use the data as this affects the
structures required.

 

[Guest]

Well now I have been challenged by Jamie and asked nicely by you.

I will review all of the OPs threads in the last 14 days and try to document
what I believe they are trying to achieve and what the problems may be.

I will add this caveat - that Database Design by Email is a very dangerous
exercise but I will detail any assumptions I make that would affect the
result.

The original poster may have given up on us. He just posted a separate
thread saying that none of us are very helpful and that his database is now
working (he has posted that message several times before, though).

One of the problems with helping scubadiver has been his total lack of
information of what he is really trying to do. I'm not sure if he is
tracking payroll paid or just hours worked, whether he has departments and/or
subdepartments. He is obsessed with tracking per week and doesn't want to
put in dates. He randomly chooses a field to be a primary key, and then
argues when told he shouldn't do that.

I'm almost tempted to say that this is a college prank, where someone who
really does know better about databases is trying to set a record of how many
frivolous posts they can generate before people give up on them. I have a
hard time believing that someone could really be this stubborn about not
learning.

 

[Immanuel Sibero]

mnature,

I can sympathize with you. I tried to help but the OP didnt even acknowledge
my posts and I don't know why he has not acknowledged my posts.... hell, I
dont know why I'm still posting on this thread trying to help him (I do have
a demanding and full time job... working on relational database design no
less <g>).

I think Jamie and Craig's arguments are both valid, the circumstances would
eventually determine which one is effective. Each circumstance is different.
The OP is quite a challenge because my posts trying to help him have
actually taken both Jamie's and Craig's methods. I initially posted to say
that he plain and simple just needs to *brush up* on relational database
design (incl. normalization). OP didnt respond to this. I then tried to
critique the OP's own design by poking holes on it and pointing out that his
tables are not normalized and the reasons why those tables are not
normalized. Still, the OP didnt not respond. I dont even know if he reads my
posts.

As I stated before in my other post, I think this is a case where the OP
knows enough about relational database design to realize that he needs help,
but he doesnt know enough about it to recognize a solution if the solution
bit him in the eye. This makes it very difficult to help him.


To use Craig's fish analogy...
You first try to teach him how to fish, if that fails then give him a fish.
The problem is, he doesnt even know it's a fish.


Immanuel Sibero