Re: [GENERAL] Employee modeling question
On Fri, Sep 5, 2014 at 11:39 AM, Rich Shepard rshep...@appl-ecosys.com wrote: On Fri, 5 Sep 2014, John McKown wrote: They are excellent. They are _not_ for beginners. The For Smarties portion is not just a play against the For Dummies series. Joe does some high powered SQL. For the purpose of developing an employee schema with departments for some, his SQL For Smarties provides very sound advice on how to proceed. Having separate company, department, and employee tables is a given. But, you might need many-to-many tables to keep track of the complex relationships. This is all covered in the chapters on DDL (Data Definition Language) and is separate from the chapters on DML (Data Manipulation Language). Good luck, Rich Thank you Rich, and apologies for the delay in getting back to this. Sometimes my job has a bad habit of getting in the way of getting work done. I've been through the first four or five chapters of the SQL For Smarties book, and I've glanced at the other two books we have, but I didn't find anything especially enlightening (and I was surprised at the number of typographical errors in the content). I have also read through the other references I was given. Although I have not completely hashed this whole situation out, I am leaning towards an exclusivity constraint on department and business, where one of the columns will be required to be null, and a check constraint on the business column that will not allow businesses that are referenced in the department table. This seems to meet all of my rules and requirements, and will also work in the case of external contracts applying to a business or a department. If this plan changes dramatically I will update this posting, and I do appreciate the advice that I received from you and everyone else. I especially appreciate being given pointers to information sources as opposed to receiving pat answers without explanations. Reading and learning will prove much more beneficial in the long run. Well, back to work. Gotta go explain to someone why two separate and unrelated tables won't model their multi step workflow too well (OK not at all really). I just love how people that can populate a spreadsheet think that makes them into data professionals. Nelson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Employee modeling question
I could store the department and business attributes with the employee, but without proper constraints the referenced department could conceivably not correspond to the referenced business. Or I could ensure that all businesses have at least one department, defaulting to the business when the business has no department, but then I'd be storing duplicate data. The other alternative I've come up with is an exclusive constraint where the employee instance can only reference a department or a business, but not both. None of these solutions seems ideal, although the exclusivity solution seems like it would work the best, and I have had to create exclusivity constraints before. So, am I missing a more obvious solution, or am I even on track here? I’ve found this « Universal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014 Hope this helps, François Beausoleil Thanks François. This is pretty generic stuff, but my first reading has got me thinking that I should at least pick up some ideas from it. I will give it a go. And I've got a new web site that I've now known about before, so thanks for that as well. Regards, Nelson I strongly suggest you read the writings of Joe Celko, he has been addressing this kind of problem for decades. Ultimately, it is very important not to confuse behaviour with entities (yes employment is behavioural) Cheers Thanks Robin. Ironically enough, our little local library has three books by Joe Celko, so looks like I may have a weekend of reading ahead of me. Nelson
Re: [GENERAL] Employee modeling question
On Fri, Sep 5, 2014 at 7:52 AM, Nelson Green nelsongree...@gmail.com wrote: Thanks Robin. Ironically enough, our little local library has three books by Joe Celko, so looks like I may have a weekend of reading ahead of me. I'm impressed that your library has him. But, unless you are _very_ good with SQL, you're going to need more than a single weekend. Unless by weekend you mean 48 hours of constant reading. I have a lot of his books. They are excellent. They are _not_ for beginners. The For Smarties portion is not just a play against the For Dummies series. Joe does some high powered SQL. I am not, and never really will be, an SQL expert. I love his books. But, at least for me, they are an intense read. Nelson -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Employee modeling question
On Fri, Sep 5, 2014 at 9:46 AM, John McKown john.archie.mck...@gmail.com wrote: On Fri, Sep 5, 2014 at 7:52 AM, Nelson Green nelsongree...@gmail.com wrote: Thanks Robin. Ironically enough, our little local library has three books by Joe Celko, so looks like I may have a weekend of reading ahead of me. I'm impressed that your library has him. But, unless you are _very_ good with SQL, you're going to need more than a single weekend. Unless by weekend you mean 48 hours of constant reading. I have a lot of his books. They are excellent. They are _not_ for beginners. The For Smarties portion is not just a play against the For Dummies series. Joe does some high powered SQL. I am not, and never really will be, an SQL expert. I love his books. But, at least for me, they are an intense read. Nelson -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown Thanks for the warning John. No, I hold no illusion that I will completely solve this problem through two days of reading. What I meant is that I will have two days away from work to get some work done. What I hope to have by the start of next week is a better idea of what I am up against. I am glad that I realized this simple problem will require a complex solution before I got too far along, and I really appreciate the learning opportunities that have been offered here. Nelson
Re: [GENERAL] Employee modeling question
On Fri, 5 Sep 2014, John McKown wrote: They are excellent. They are _not_ for beginners. The For Smarties portion is not just a play against the For Dummies series. Joe does some high powered SQL. I read Joe Celko's columns in Database Advisor and a couple of other magazines in the '80s, and have read most of his books. For the purpose of developing an employee schema with departments for some, his SQL For Smarties provides very sound advice on how to proceed. Having separate company, department, and employee tables is a given. But, you might need many-to-many tables to keep track of the complex relationships. This is all covered in the chapters on DDL (Data Definition Language) and is separate from the chapters on DML (Data Manipulation Language). Good luck, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Employee modeling question
Good morning, Hopefully this is the correct place to ask this type of question. I am in the early stages of designing a system to track employee information, including some aspects of their payroll, one of which is the source of the salary funds within the business. I need to make this generic enough to accommodate instances where an employee's salary is sourced from a department's budget, as well as those salaries of employees that are employed by companies too small to be departmentalized. So when an employee is employed by a department, the department is the source of the employee's salary, and the business is the department's parent entity. But when an employee is employed by a business that has no departments, the business entity is the source of the employee's salary, and the parent entity. I am struggling with the correct logical implementation of this scenario. So basically I have a situation where an employee will most likely be associated with a department within a business, but this can not be guaranteed, and I'm not 100% sure how to handle this. I am going to face the same problem with contracts where a department can out-source a function, or a business can. I think there may even be instances where a business with departments may out-source functionality that is not charged to a department. I could store the department and business attributes with the employee, but without proper constraints the referenced department could conceivably not correspond to the referenced business. Or I could ensure that all businesses have at least one department, defaulting to the business when the business has no department, but then I'd be storing duplicate data. The other alternative I've come up with is an exclusive constraint where the employee instance can only reference a department or a business, but not both. None of these solutions seems ideal, although the exclusivity solution seems like it would work the best, and I have had to create exclusivity constraints before. So, am I missing a more obvious solution, or am I even on track here? Thanks in advance and regards, Nelson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Employee modeling question
Hello Nelson, Le 2014-09-04 à 10:39, Nelson Green nelsongree...@gmail.com a écrit : Good morning, Hopefully this is the correct place to ask this type of question. I am in the early stages of designing a system to track employee information, including some aspects of their payroll, one of which is the source of the salary funds within the business. I need to make this generic enough to accommodate instances where an employee's salary is sourced from a department's budget, as well as those salaries of employees that are employed by companies too small to be departmentalized. So when an employee is employed by a department, the department is the source of the employee's salary, and the business is the department's parent entity. But when an employee is employed by a business that has no departments, the business entity is the source of the employee's salary, and the parent entity. I am struggling with the correct logical implementation of this scenario. So basically I have a situation where an employee will most likely be associated with a department within a business, but this can not be guaranteed, and I'm not 100% sure how to handle this. I am going to face the same problem with contracts where a department can out-source a function, or a business can. I think there may even be instances where a business with departments may out-source functionality that is not charged to a department. I could store the department and business attributes with the employee, but without proper constraints the referenced department could conceivably not correspond to the referenced business. Or I could ensure that all businesses have at least one department, defaulting to the business when the business has no department, but then I'd be storing duplicate data. The other alternative I've come up with is an exclusive constraint where the employee instance can only reference a department or a business, but not both. None of these solutions seems ideal, although the exclusivity solution seems like it would work the best, and I have had to create exclusivity constraints before. So, am I missing a more obvious solution, or am I even on track here? I’ve found this « Universal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014 Hope this helps, François Beausoleil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Employee modeling question
On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil franc...@teksol.info wrote: Hello Nelson, Le 2014-09-04 à 10:39, Nelson Green nelsongree...@gmail.com a écrit : Good morning, Hopefully this is the correct place to ask this type of question. I am in the early stages of designing a system to track employee information, including some aspects of their payroll, one of which is the source of the salary funds within the business. I need to make this generic enough to accommodate instances where an employee's salary is sourced from a department's budget, as well as those salaries of employees that are employed by companies too small to be departmentalized. So when an employee is employed by a department, the department is the source of the employee's salary, and the business is the department's parent entity. But when an employee is employed by a business that has no departments, the business entity is the source of the employee's salary, and the parent entity. I am struggling with the correct logical implementation of this scenario. So basically I have a situation where an employee will most likely be associated with a department within a business, but this can not be guaranteed, and I'm not 100% sure how to handle this. I am going to face the same problem with contracts where a department can out-source a function, or a business can. I think there may even be instances where a business with departments may out-source functionality that is not charged to a department. I could store the department and business attributes with the employee, but without proper constraints the referenced department could conceivably not correspond to the referenced business. Or I could ensure that all businesses have at least one department, defaulting to the business when the business has no department, but then I'd be storing duplicate data. The other alternative I've come up with is an exclusive constraint where the employee instance can only reference a department or a business, but not both. None of these solutions seems ideal, although the exclusivity solution seems like it would work the best, and I have had to create exclusivity constraints before. So, am I missing a more obvious solution, or am I even on track here? I’ve found this « Universal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014 Hope this helps, François Beausoleil Thanks François. This is pretty generic stuff, but my first reading has got me thinking that I should at least pick up some ideas from it. I will give it a go. And I've got a new web site that I've now known about before, so thanks for that as well. Regards, Nelson
Re: [GENERAL] Employee modeling question
Robin St.Clair On 04/09/2014 20:44, Nelson Green wrote: On Thu, Sep 4, 2014 at 9:48 AM, François Beausoleil franc...@teksol.info mailto:franc...@teksol.info wrote: Hello Nelson, Le 2014-09-04 à 10:39, Nelson Green nelsongree...@gmail.com mailto:nelsongree...@gmail.com a écrit : Good morning, Hopefully this is the correct place to ask this type of question. I am in the early stages of designing a system to track employee information, including some aspects of their payroll, one of which is the source of the salary funds within the business. I need to make this generic enough to accommodate instances where an employee's salary is sourced from a department's budget, as well as those salaries of employees that are employed by companies too small to be departmentalized. So when an employee is employed by a department, the department is the source of the employee's salary, and the business is the department's parent entity. But when an employee is employed by a business that has no departments, the business entity is the source of the employee's salary, and the parent entity. I am struggling with the correct logical implementation of this scenario. So basically I have a situation where an employee will most likely be associated with a department within a business, but this can not be guaranteed, and I'm not 100% sure how to handle this. I am going to face the same problem with contracts where a department can out-source a function, or a business can. I think there may even be instances where a business with departments may out-source functionality that is not charged to a department. I could store the department and business attributes with the employee, but without proper constraints the referenced department could conceivably not correspond to the referenced business. Or I could ensure that all businesses have at least one department, defaulting to the business when the business has no department, but then I'd be storing duplicate data. The other alternative I've come up with is an exclusive constraint where the employee instance can only reference a department or a business, but not both. None of these solutions seems ideal, although the exclusivity solution seems like it would work the best, and I have had to create exclusivity constraints before. So, am I missing a more obvious solution, or am I even on track here? I’ve found this « Universal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014 Hope this helps, François Beausoleil Thanks François. This is pretty generic stuff, but my first reading has got me thinking that I should at least pick up some ideas from it. I will give it a go. And I've got a new web site that I've now known about before, so thanks for that as well. Regards, Nelson I strongly suggest you read the writings of Joe Celko, he has been addressing this kind of problem for decades. Ultimately, it is very important not to confuse behaviour with entities (yes employment is behavioural) Cheers
