Learning Normalisation EMS as Sample (1 Viewer)

Lightwave

Ad astra
Local time
Today, 21:57
Joined
Sep 27, 2004
Messages
1,521
Hi jmq - I actually thought quite a bit about this during the night. I used our discussion as inspiration for some thoughts that I expanded on and put in a blog post. You can actually model the relationship we have been investigating as either many to many / one to many / one to one or as a key value relationship. See here.

Alternate ways of modelling relationships in people
 
Last edited:

jmq

Registered User.
Local time
Today, 14:57
Joined
Oct 4, 2017
Messages
87
Lightwave,

the idea was very interesting! i will study on this further! Thank you!

it's like creating a Class namely Person then Children. all are generic. the difference is the Class "Children" has a field called [mother] and [father].

i think we can even extend this to having only a Class Person. because a child is also a Person and a "Mother and Father" have also a Mother and father.

with that can we have 1 table alone? namely Person with field[Mother]&[Father]?

:)

anyway. this is really out of my project. jdraw might see this and flag me again. lol :D
 

Lightwave

Ad astra
Local time
Today, 21:57
Joined
Sep 27, 2004
Messages
1,521
i think we can even extend this to having only a Class Person. because a child is also a Person and a "Mother and Father" have also a Mother and father.

with that can we have 1 table alone? namely Person with field[Mother]&[Father]?

Sounds like you've totally got it.

And now we are back to a single table

It would seem that after our discussion my conclusion is..

For many to many relationships where one of the two one to many relationships is finite (eg 2 parents to 1 child) data can modelled either using many to many / one to many / one to one or even a key value structure. The chosen structure will be a design choice taking into consideration the extent of how finite the relationship of one of the two one to many relationships are ,the importance of collecting each field of information and the validation available or required within the UI - all could be equally optimal
 
Last edited:

Lightwave

Ad astra
Local time
Today, 21:57
Joined
Sep 27, 2004
Messages
1,521
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.

I really like this structure for contact information I consider this the exact same as the key value database model.

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

jdraw

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Jan 23, 2006
Messages
15,364
jmq,
Things seem to be progressing-- good stuff.
I helped a poster with some info on hierarchy a while back --similar in concept to your Parent (Mother/Father) and Child. You may find it helpful. His application was about Dogs and Parents(Sire, Dam).

Here is a link to that thread, but they key point re hierarchy is in posts #34 , #36 and #37

Lightwave/Mark,
I like the blog articles ---always nice to see other's thoughts and descriptions.
 

jmq

Registered User.
Local time
Today, 14:57
Joined
Oct 4, 2017
Messages
87
jdraw,

i guess this is the reason why open source software always is better than proprietary ones. simply because of the collaborated idea/logic of infinite participants. :)

i like this. :)

i kinda grasp the idea of these er stuff in Lightwave's model. very identical to object oriented programming. i never thought that entity is a class. you can create lots of it by instantiation. here in db - row is the instance of the entity. :) very clear. :)
 

jmq

Registered User.
Local time
Today, 14:57
Joined
Oct 4, 2017
Messages
87
i guess this is enough. time to build the physical db.. i will get in touch every time i'm stuck coding..
 

Mark_

Longboard on the internet
Local time
Today, 14:57
Joined
Sep 12, 2017
Messages
2,111
I really like this structure for contact information I consider this the exact same as the key value database model.

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

Lightwave,

For tracking names I've had to deal with some strange issues. For one project we had to store not only the name the customer had on their ID, but also track their "Also Known By"s. This became more interesting because, as this was for the gaming industry, most of the floor personnel only knew the individuals by their "Also Know By". For reasons I will never understand there are those who wish to be known by different names in different places.

This became a small subsystem that tied into all other portions, including looking up the real person when it came time to track cashing out. Regulators need to know how much a given transaction is for and to whom it is paid. If the cashier knows you as "Lightwave", we still had to tie that back to your actual name.

System included demographics for given names so that we could distinguish between the assorted "Big Reds" and "Slim Shadys" out there.
 

Users who are viewing this thread

Top Bottom