Learning Normalisation EMS as Sample (2 Viewers)

[jmq]

hi guys!

normalisation again.

is this normalised?

thanks?

 

[CJ_London]

depends on what you are using this for but in principle looks OK with the possible exception of the rates table.

What happens if the rates change? suggest you need a 'rate from' field. And what happens if you add another rate type? - suggest this table should be

RateID
PosID
RateTypeID
Rate
RateFrom

with another rate type table

you probably want to change age to dob in dependants table and add a 'date left' field in the designations table (only populated when employee leaves the company, not change roles)

 

[jmq]

CJ_London,

thank you for the time to reply.. greatly appreciated!

i have here what you have suggested. And I hope i get it right.

here is the shared link of the model. i have comment on it. please try to answer. thanks.

red are new ones.

https://www.lucidchart.com/invitations/accept/d0354b89-aa52-4be5-943c-ad9bf32588e7

Purpose of this db:

1. I intend to create a database of all employees. obviously
2. I want to keep a record of their dependents
3. I want to keep all records of their positions from the start (kind of history)
4. I want to keep all records of rates (history again)

then if i get this right i'll expand this to payroll.. hopefully. including cash advances.

Thank you again!

 

[CJ_London]

that looks OK to me for what you describe, only other comment is do you need to maintain a record of employee change of address? If so, you will need to split this out to a separate table with a date field aka designations.

Finally on rates, do all employees in the same position get the same rate, regardless of dept?

 

[Lightwave]

You could arguably merge the dependents and employees table and just call it a persons table and then create a further table called PersonJunction.

This table would hold the links between individuals.

Such a table should include at a minimum
PrimaryKey (autonumber)
FKIDPerson-Guardian
FKIDPerson-Dependent

You will see that you are repeating the structure of the employees in the dependents table. Merging them and then creating a junction table is a stricter application of the rules of normalization and is preferable if an individual can be both a dependent and an employee. This is possibly an edge case for you but it can facilitate the creation of lookups for a field where the value should be chosen from either dependents or employees.

 

[jmq]

do you need to maintain a record of employee change of address?

do all employees in the same position get the same rate, regardless of dept?

yes i need to track their addresses.

some department has the same rate per position while other has different rate.

 

[jmq]

You could arguably merge the dependents and employees table and just call it a persons table and then create a further table called PersonJunction.

This table would hold the links between individuals.

Such a table should include at a minimum
PrimaryKey (autonumber)
FKIDPerson-Guardian
FKIDPerson-Dependent

You will see that you are repeating the structure of the employees in the dependents table. Merging them and then creating a junction table is a stricter application of the rules of normalization and is preferable if an individual can be both a dependent and an employee. This is possibly an edge case for you but it can facilitate the creation of lookups for a field where the value should be chosen from either dependents or employees.

i like the idea! i just want to clarify some things..

when you say junction, you mean a

many to many relationship?

so if i merge the

dependents+employees = persons

then create a junction labelled

 personJunction

i think this will result to

one to many?

cause i only have 2 tables now

persons and personJunction

 

[CJ_London]

some department has the same rate per position while other has different rate.

in that case your tables and relationships need more work. since its current structure will not support that requirement. Your project is starting to suffer from 'mission creep' in that you have not fully defined all the requirements. Suggest you sit down and clarify them in full

 

[jdraw]

jmq,

Have you completed your earlier project? Or just extending your Normalization learning?
As others have said, getting a clear description of what is required is a key first step. As CJ pointed out, "mission creep" is a red flag --- look to your requirements and verify the scope of your project.

 

[jmq]

jmq,

Have you completed your earlier project? Or just extending your Normalization learning?
As others have said, getting a clear description of what is required is a key first step. As CJ pointed out, "mission creep" is a red flag --- look to your requirements and verify the scope of your project.

jdraw,

I'm doing it now. i guess i have to focus on that first. and come back to this later. hehehe

i hope i can submit that here as sample db.

 

[jdraw]

If you submit a db here, include a description of the business the db is intended to support.
You need to be able to compare the data model/ database structure (tables and relationships) to the business facts identified in the description of the business.

Sounds like you're learning -- making a few models and working through them and adjusting as needed is great experience. Much like sailing --you can read books and theory, but at some point you have to get out on the water and get wet.

 

[Mark_]

jmq,

As you will run into this quickly, you will want a child file off of either "Personnel" (If you merge "Dependents" and "Employees", a good idea since two employees could be married to each other) or off "Employees"/"Dependents" to hold contact information.

For my preference the format for this file is simple;
PK - AutoNumbered
FK - Parent PK
ContactType - String - limited based off of a list you provide. Things like "Phone", "EMail", "EMER Phone", "Grandma's Phone", what have you.
Contact - String - Holds the actual contact information. Optionally formatted based off of ContactType.

Simple single storage that avoids all of the issues of keeping multiple ways of contacting the same person.

 

[Lightwave]

when you say junction you mean a many to many relationship

Yes

so if I merge the dependents+employees = persons then create a junction labelled personJunction I think this will result to

one to many? cause I only have 2 tables now persons and personJunction

When people come across many to many relationships the first thing they learn is that a many to many relationships in MS Access actually consists of 2 one to many relationships (as you point out) and this would normally be shown in a relationship diagram between three tables.

Digging a bit deeper most don't realise but a single table can be aliased. So in the relationship diagram if you initially add a table you can then add the same table again. The second copy of the same table is given an alias name by MS Access automatically, usually this consists of the tablename an underscore and then a number eg _1. This is NOT a second copy this is the same table. You now have three objects with which to create your 2 one to many relationships.

 

[jmq]

If you submit a db here, include a description of the business the db is intended to support.
You need to be able to compare the data model/ database structure (tables and relationships) to the business facts identified in the description of the business.

Sounds like you're learning -- making a few models and working through them and adjusting as needed is great experience. Much like sailing --you can read books and theory, but at some point you have to get out on the water and get wet.

jdraw,

i getting poetic now.

 

[jmq]

underscore and then a number eg _1. This is NOT a second copy this is the same table. You now have three objects with which to create your 2 one to many relationships.

oh i see. i noticed that every time i mistakenly drag a table to a relationship diagram. so if i have tableA and tableB to have that many to many without manually creating a junction table, i'll just drag tableA again to the relationship diagram thus giving me a tableA, tableA_1, and tableB now.

now what method should it be followed? this dragging again or creating a junction table?

this is interesting!

 

[Lightwave]

Here's a link to the kind of relationships you should then set up.

http://rounduptheusualsuspects.org/wp-content/uploads/2017/11/ManytoManyRelationships.png

 

[jdraw]

jmq,

You might want to watch this youtube by Dr. Soper. There are others in his series.

 

[jmq]

Here's a link to the kind of relationship you should then set up.

http://rounduptheusualsuspects.org/wp-content/uploads/2017/11/ManytoManyRelationships.png

oh i get it. this is great! thank you!

 

[jmq]

[qoute]
I was like you I suddenly discovered something which probably had been under my nose for years. You quickly realise that as much as you know you maybe don't know anything.
[/quote]

what da?! lightwave, you and jdraw are brothers or something? you have a tongue like jdraw. very poetic in a very crazy way!

 

[Lightwave]

The actual Biological Parent to Biological Child relationship is somewhat more subtle than the classic description in books would have you believe (they constantly equate the parent child relationship to a one to many relationship).

Strictly strictly speaking biological parent to biological child is a many to many relationship (which in MS Access is a recursion of 2 one to many relationships) but it is a specific version of a many to many relationship. One parent can have many (read infinite) children but one child can only ever have 2 parents - one biological mother linked to one biological father (ignoring latest developments in science which just totally confuse things) . Of course a one to two relationship is still a type of of one to many relationship it is a more specific type of one to many relationship but one to many nonetheless - so to model all relationships you still need two one to many links. If you consider the unique combination of mother and father as a group in itself then you can model all relationships again as only consisting of one group of one to many relationships rather than as two one to many relationships (which we presently have defined as one one to many and one one to two relationship). To do this you would have to make a mother and father a unique pair who can have infinite number of children but a child can ONLY have one mother father pair.

Explaining mathematics, in this case group theory, in words and then throwing in recursion really gets people confused its not really surprising people don't usually get it first time.