Re: Normalization question

2017-11-08 Thread John Baughman via 4D_Tech 
Of course. If, however, you go down that rabbit hole with your eyes open…

Take for example using Employee_Number as I mentioned. If business rules do not 
allow deletion or changing of this field once it is created, then I see no 
problem using it as a relational key. For an Employee Number this usually goes 
beyond the 4D database to other accounting programs, payroll systems, etc. 
Changing an employee number would reek havoc throughout a company.

I guess instead of a “user definable field” it is a “one time user definable 
field”.

I too avoid using a user definable field as a relational/unique field, but o 
occasion I have done so, but only if I was certain that the field had no chance 
of ever breaking the relationship due to user intervention.

John 

> On Nov 8, 2017, at 12:48 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> ** NO ** user definable data should be used as relational/unique key 
> value.

John Baughman
Kailua, Hawaii
(808) 262-0328
john...@hawaii.rr.com





**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Kirk Brooks via 4D_Tech 
Doug,
That's a really good resource for ideas for structuring things. I haven't
looked at in a long time - it's grown quite a lot.

On Wed, Nov 8, 2017 at 12:23 PM, Douglas von Roeder via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> This is a link  to a
> treasure trove of data models. I checked a couple of the models in the
> "employees" section and the generic model
>  generic_model/index.htm>
> might provide some ideas.
>

-- 
Kirk Brooks
San Francisco, CA
===

*The only thing necessary for the triumph of evil is for good men to do
nothing.*

*- Edmund Burke*
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Chip Scheide via 4D_Tech 
meaningful can be in context:
- a UUID floating alone in an ocean of text has no meaning.
- a UUID in a unique (indexed?) field of a relational database has 
meaning - it is the some identifier
- 'Joe' has no meaning, except in context. You might expect that it is 
a name, but..
Hey Joe, get me some Joe!


:)



On Wed, 8 Nov 2017 17:08:10 -0600, Keith Culotta via 4D_Tech wrote:
> Something that stood out in a recent thread about the definition of 
> the Relational Model was a statement that said related fields should 
> contain meaningful information.  This was a surprise, especially 
> since the use of UUIDs has been such a convenience.  I took 
> 'meaningful' to imply 'user definable' (with guidance of course), in 
> spite of the headaches it can lead to.
> 
> Keith - CDI
> 
>> On Nov 8, 2017, at 4:48 PM, Chip Scheide via 4D_Tech 
>> <4d_tech@lists.4d.com> wrote:
>> 
>> ** NO ** user definable data should be used as relational/unique key 
>> value.
>> generate your own internal values for relations, longint, UUIDs, 
>> variations on the Batman Logo, anything that the user has no say in 
>> what it is nor how it is used.
>> 
>> On Wed, 8 Nov 2017 17:38:38 -0500, Chip Scheide via 4D_Tech wrote:
>>> 
 If, as is the case in my situation, one has a unique field in the 
 Employee table that is user recognizable such as an Employee Number 
>> As I suggested, the employee number could change, be reused, they could 
>> change the form of the ID, from say 10 digits, to a 4 character 
>> alphanumeric, or to a retinal scan... who knows!!! And don't forget 
>> that value will likely be typed (entered) at some point and there are 
>> typos too.
>> 
>> Melinda Enters Joe's ID as 1234578 (which is Fred's), instead of 
>> 1234587. It takes a month to find the issue (payday) as Joe doesn't get 
>> a pay check and Fred gets an extra large one (way to go Fred! good work 
>> here is a bonus).  I think you can see where this goes...how do you 
>> untangle *that* mess as all of your internal data is tied to the 
>> mis-entered employee ID.
>> 
>> my 2cents
>> 
>> 
>> 
>> ---
>> Gas is for washing parts
>> Alcohol is for drinkin'
>> Nitromethane is for racing 
>> **
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: http://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>> **
> 
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Keith Culotta via 4D_Tech 
Something that stood out in a recent thread about the definition of the 
Relational Model was a statement that said related fields should contain 
meaningful information.  This was a surprise, especially since the use of UUIDs 
has been such a convenience.  I took 'meaningful' to imply 'user definable' 
(with guidance of course), in spite of the headaches it can lead to.

Keith - CDI

> On Nov 8, 2017, at 4:48 PM, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> ** NO ** user definable data should be used as relational/unique key 
> value.
> generate your own internal values for relations, longint, UUIDs, 
> variations on the Batman Logo, anything that the user has no say in 
> what it is nor how it is used.
> 
> On Wed, 8 Nov 2017 17:38:38 -0500, Chip Scheide via 4D_Tech wrote:
>> 
>>> If, as is the case in my situation, one has a unique field in the 
>>> Employee table that is user recognizable such as an Employee Number 
> As I suggested, the employee number could change, be reused, they could 
> change the form of the ID, from say 10 digits, to a 4 character 
> alphanumeric, or to a retinal scan... who knows!!! And don't forget 
> that value will likely be typed (entered) at some point and there are 
> typos too.
> 
> Melinda Enters Joe's ID as 1234578 (which is Fred's), instead of 
> 1234587. It takes a month to find the issue (payday) as Joe doesn't get 
> a pay check and Fred gets an extra large one (way to go Fred! good work 
> here is a bonus).  I think you can see where this goes...how do you 
> untangle *that* mess as all of your internal data is tied to the 
> mis-entered employee ID.
> 
> my 2cents
> 
> 
> 
> ---
> Gas is for washing parts
> Alcohol is for drinkin'
> Nitromethane is for racing 
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Chip Scheide via 4D_Tech 
** NO ** user definable data should be used as relational/unique key 
value.
generate your own internal values for relations, longint, UUIDs, 
variations on the Batman Logo, anything that the user has no say in 
what it is nor how it is used.

On Wed, 8 Nov 2017 17:38:38 -0500, Chip Scheide via 4D_Tech wrote:
> 
>> If, as is the case in my situation, one has a unique field in the 
>> Employee table that is user recognizable such as an Employee Number 
As I suggested, the employee number could change, be reused, they could 
change the form of the ID, from say 10 digits, to a 4 character 
alphanumeric, or to a retinal scan... who knows!!! And don't forget 
that value will likely be typed (entered) at some point and there are 
typos too.

Melinda Enters Joe's ID as 1234578 (which is Fred's), instead of 
1234587. It takes a month to find the issue (payday) as Joe doesn't get 
a pay check and Fred gets an extra large one (way to go Fred! good work 
here is a bonus).  I think you can see where this goes...how do you 
untangle *that* mess as all of your internal data is tied to the 
mis-entered employee ID.

my 2cents



---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Alan Chan via 4D_Tech 
Table
Project Master
Employee Master
Project member

Project master <- Project member -> Employee master

Project member table contains Role (integer) 0=regular member; 1=role A; 2=role 
B; 3=whatever role

Alan Chan

4D iNug Technical <4d_tech@lists.4d.com> writes:
>Given a Project table and an Employee table where Employees can be Project 
>Principles, Project Managers, and Project Supervisors, what would be the 
>proper way to create a normalized table structure in 4D. In the end an 
>assigned employee’s
>information, like name for example, would automatically be displayed in the 
>project’s output form for each of these assignments.
>
>I am pretty sure using the proper Select/Join commands in SQL this could 
>easily be done, but I am falling short with 4D as none of these structures 
>work...
>
>[Employees]ID  <——— [Projects]PrincipleEmployeeID
> <——— [Projects]ProjectMangeEmplyeerID 
> <——— [Projects]SupervisorEmployeeID
>
>OR using linked tables….
>   (There is only one record in each linking table for 
> each employee assigned a role)
>[Employees]ID  
><———[Principles]EmployeeID<———---[Projects]PrincipleEmployeeID
><———[ProjectManagers]EmployeeID <——— 
> [Projects]ProjectMangeEmplyeerID
><———[Supervisors]EmployeeID <——— 
> ---[Projects]SupervisorEmployeeID
>
>The only way I can see to make it work is to denormalize it and put the 
>pertinent data from the Employee table into the linking tables in which case 
>the relationship between the employee table and the linking tables become 
>irrelevant. 
>
>I have no problem denormalizing, but thought this might be a good exercise to 
>retain a few more cells in my aging brain. ;-)
>
>John
>
>
>John Baughman
>Kailua, Hawaii
>(808) 262-0328
>john...@hawaii.rr.com
>
>
>
>
>
>**
>4D Internet Users Group (4D iNUG)
>FAQ:  http://lists.4d.com/faqnug.html
>Archive:  http://lists.4d.com/archives.html
>Options: http://lists.4d.com/mailman/options/4d_tech
>Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>**

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Normalization question

2017-11-08 Thread Dennis, Neil via 4D_Tech 
My number three choice that would work in an output form would be this:

> [Employees]ID  <——— [Projects]PrincipleEmployeeID
><——— [Projects]ProjectMangeEmplyeerID 
><——— [Projects]SupervisorEmployeeID

Your list form would consist of two or three variables housing the employee name
In the form on load event, create an array cache of ID and Names
In the form on display code fill in the variables on the form

My number two choice would be to model the tables as below

> [Project]<--[ProjectEmployeeType]-->   [Employee]  

You could use the same variable/cache approach in the output form with this 
table structure, however it is a bit more complex to pull in the data during 
the on load. However the table structure is a more flexible design.

My number one choice is instead of an output form use a listbox, you could use 
a single SQL query with multiple joins to the same table to pull in the data 
(no cache and on display).

Neil











Privacy Disclaimer: This message contains confidential information and is 
intended only for the named addressee. If you are not the named addressee you 
should not disseminate, distribute or copy this email. Please delete this email 
from your system and notify the sender immediately by replying to this email.  
If you are not the intended recipient you are notified that disclosing, 
copying, distributing or taking any action in reliance on the contents of this 
information is strictly prohibited.

The Alternative Investments division of UMB Fund Services provides a full range 
of services to hedge funds, funds of funds and private equity funds.  Any tax 
advice in this communication is not intended to be used, and cannot be used, by 
a client or any other person or entity for the purpose of (a) avoiding 
penalties that may be imposed on any taxpayer or (b) promoting, marketing, or 
recommending to another party any matter addressed herein.
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Douglas von Roeder via 4D_Tech 
John:

This is a link  to a
treasure trove of data models. I checked a couple of the models in the
"employees" section and the generic model

might provide some ideas.

Reading through your table names, you're defining Principles, PM's, and
Supervisors as separate entities. That may be justifiable but my tendency
would be to see those as one or many "roles" for one or many organizations,
each role having a start date, end date, etc.

The generic model has "assignments" but doesn't support the concept of
"role". Hmm. A table defining a role would be a recursively linked table to
define relationship(s) between employees.

"Data Model Patterns" by David Hay could be a good source of insight. It's
not an easy read but the author reviews numerous enterprise data models in
detail.




--
Douglas von Roeder
949-336-2902

On Wed, Nov 8, 2017 at 12:06 PM, John Baughman via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> Given a Project table and an Employee table where Employees can be Project
> Principles, Project Managers, and Project Supervisors, what would be the
> proper way to create a normalized table structure in 4D. In the end an
> assigned employee’s information, like name for example, would automatically
> be displayed in the project’s output form for each of these assignments.
>
> I am pretty sure using the proper Select/Join commands in SQL this could
> easily be done, but I am falling short with 4D as none of these structures
> work...
>
> [Employees]ID  <——— [Projects]PrincipleEmployeeID
>  <——— [Projects]ProjectMangeEmplyeerID
>  <——— [Projects]SupervisorEmployeeID
>
> OR using linked tables….
> (There is only one record in each linking table
> for each employee assigned a role)
> [Employees]ID  <———[Principles]EmployeeID<———---[Projects]
> PrincipleEmployeeID
>  <———[ProjectManagers]EmployeeID <——— [Projects]
> ProjectMangeEmplyeerID
>  <———[Supervisors]EmployeeID <——— ---[Projects]
> SupervisorEmployeeID
>
> The only way I can see to make it work is to denormalize it and put the
> pertinent data from the Employee table into the linking tables in which
> case the relationship between the employee table and the linking tables
> become irrelevant.
>
> I have no problem denormalizing, but thought this might be a good exercise
> to retain a few more cells in my aging brain. ;-)
>
> John
>
>
> John Baughman
> Kailua, Hawaii
> (808) 262-0328
> john...@hawaii.rr.com
>
>
>
>
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Normalization question

2017-11-08 Thread Keith Culotta via 4D_Tech 
[Project]<--[ProjectEmployeeType]-->   [Employee]  ?

Keith - CDI

> On Nov 8, 2017, at 2:06 PM, John Baughman via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Given a Project table and an Employee table where Employees can be Project 
> Principles, Project Managers, and Project Supervisors, what would be the 
> proper way to create a normalized table structure in 4D. In the end an 
> assigned employee’s information, like name for example, would automatically 
> be displayed in the project’s output form for each of these assignments.
> 
> I am pretty sure using the proper Select/Join commands in SQL this could 
> easily be done, but I am falling short with 4D as none of these structures 
> work...
> 
> [Employees]ID  <——— [Projects]PrincipleEmployeeID
> <——— [Projects]ProjectMangeEmplyeerID 
> <——— [Projects]SupervisorEmployeeID
> 
> OR using linked tables….
>   (There is only one record in each linking table for 
> each employee assigned a role)
> [Employees]ID  
> <———[Principles]EmployeeID<———---[Projects]PrincipleEmployeeID
><———[ProjectManagers]EmployeeID <——— 
> [Projects]ProjectMangeEmplyeerID
><———[Supervisors]EmployeeID <——— 
> ---[Projects]SupervisorEmployeeID
> 
> The only way I can see to make it work is to denormalize it and put the 
> pertinent data from the Employee table into the linking tables in which case 
> the relationship between the employee table and the linking tables become 
> irrelevant. 
> 
> I have no problem denormalizing, but thought this might be a good exercise to 
> retain a few more cells in my aging brain. ;-)
> 
> John
> 
> 
> John Baughman
> Kailua, Hawaii
> (808) 262-0328
> john...@hawaii.rr.com
> 
> 
> 
> 
> 
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Normalization question

2017-11-08 Thread John Baughman via 4D_Tech 
Given a Project table and an Employee table where Employees can be Project 
Principles, Project Managers, and Project Supervisors, what would be the proper 
way to create a normalized table structure in 4D. In the end an assigned 
employee’s information, like name for example, would automatically be displayed 
in the project’s output form for each of these assignments.

I am pretty sure using the proper Select/Join commands in SQL this could easily 
be done, but I am falling short with 4D as none of these structures work...

[Employees]ID  <——— [Projects]PrincipleEmployeeID
 <——— [Projects]ProjectMangeEmplyeerID 
 <——— [Projects]SupervisorEmployeeID

OR using linked tables….
(There is only one record in each linking table for 
each employee assigned a role)
[Employees]ID  
<———[Principles]EmployeeID<———---[Projects]PrincipleEmployeeID
 <———[ProjectManagers]EmployeeID <——— 
[Projects]ProjectMangeEmplyeerID
 <———[Supervisors]EmployeeID <——— 
---[Projects]SupervisorEmployeeID

The only way I can see to make it work is to denormalize it and put the 
pertinent data from the Employee table into the linking tables in which case 
the relationship between the employee table and the linking tables become 
irrelevant. 

I have no problem denormalizing, but thought this might be a good exercise to 
retain a few more cells in my aging brain. ;-)

John


John Baughman
Kailua, Hawaii
(808) 262-0328
john...@hawaii.rr.com





**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**