|
|
You're in the
sql server programming
group:1, 2, or 3 Tables (Data Modeling Question)
sql server programming:
I'm modeling some entity that could reasonably be considered to be [one
entity] or [two entities] --- people which are customers and employees. AFAIK, these are my three basic options: OPTION 1: Have one table, People, with some "flag column" that tells us which kind of person we're dealing with. This one table would obviously have columns that are relevant to only one person type (e.g., hire date is applicable to only the employees). Such columns would always be NULL for rows of "the other" type (e.g., customers). OPTION 2: Have two tables - one for Customers and one for Employees. OPTION 3. Have three tables - one for People that has common attributes, and then one each for Customers and Employees My Question: What are some general guidelines I should consider for deciding which of these options to implement? Alternatively, which would YOU tend to go with and why? Beyond general guidance I'm particularly interested in option 3; what are some of the pros and cons of having those 3 tables? Just looking for general guidance on this important decision I have to make. Thank you for your time and consideration. -Jeff
I would go for option 3, which is to have 1 Persons table and then a table
each for Customers and Employees. Since all common attributes are grouped together, there is no duplication of information. Also, since customers and employees are separated, any domain logic that is specific for each type can be separately applied. From a application tier design perspective, you can create a Persons class and have Customers and Employees inherited from it. So it falls naturally into an object design too. When time comes for querying, by using appropriate views with joins, you can just query the information that you need and merge them when needed also. -- HTH, SriSamp Email: srisamp@gmail.com Blog: http://blogs.sqlxml.org/srinivassampath URL: http://www32.brinkster.com/srisamp [quoted text, click to view] "Jeff S" <A@B.COM> wrote in message news:e28IWY4MGHA.668@TK2MSFTNGP11.phx.gbl... > I'm modeling some entity that could reasonably be considered to be [one > entity] or [two entities] --- people which are customers and employees. > > AFAIK, these are my three basic options: > > OPTION 1: > Have one table, People, with some "flag column" that tells us which kind > of person we're dealing with. This one table would obviously have columns > that are relevant to only one person type (e.g., hire date is applicable > to only the employees). Such columns would always be NULL for rows of "the > other" type (e.g., customers). > > OPTION 2: > Have two tables - one for Customers and one for Employees. > > OPTION 3. > Have three tables - one for People that has common attributes, and then > one each for Customers and Employees > > My Question: What are some general guidelines I should consider for > deciding which of these options to implement? Alternatively, which would > YOU tend to go with and why? Beyond general guidance I'm particularly > interested in option 3; what are some of the pros and cons of having those > 3 tables? > > Just looking for general guidance on this important decision I have to > make. > > Thank you for your time and consideration. > > -Jeff >
The question you need to ask is: can an Employee also be a Customer? If the
answer is anything but a resounding "NO!," then the common columns in Customers and Employees should be extracted into a People table to avoid redundancy. [quoted text, click to view] "Jeff S" <A@B.COM> wrote in message news:e28IWY4MGHA.668@TK2MSFTNGP11.phx.gbl... > I'm modeling some entity that could reasonably be considered to be [one > entity] or [two entities] --- people which are customers and employees. > > AFAIK, these are my three basic options: > > OPTION 1: > Have one table, People, with some "flag column" that tells us which kind > of person we're dealing with. This one table would obviously have columns > that are relevant to only one person type (e.g., hire date is applicable > to only the employees). Such columns would always be NULL for rows of "the > other" type (e.g., customers). > > OPTION 2: > Have two tables - one for Customers and one for Employees. > > OPTION 3. > Have three tables - one for People that has common attributes, and then > one each for Customers and Employees > > My Question: What are some general guidelines I should consider for > deciding which of these options to implement? Alternatively, which would > YOU tend to go with and why? Beyond general guidance I'm particularly > interested in option 3; what are some of the pros and cons of having those > 3 tables? > > Just looking for general guidance on this important decision I have to > make. > > Thank you for your time and consideration. > > -Jeff >
Okay, that makes sense... now, how do I go about "knowing" if a given person
is a Customer or Employee or both? It seems I'd have to query both tables and get some count, perhaps using WHERE EXISTS. Is there a more elegant way? Thanks [quoted text, click to view] "Robert Bravery" <me@u.com> wrote in message news:ucTJPp8MGHA.3936@TK2MSFTNGP12.phx.gbl... > HI, > I would go with SriSamp on this. Especially as an added complication where > an employee can also be a customer, You wouldnt want to enter common > things > like name etc twice. By having two tables, employee and customer, with > forein keys, you can look at him as an employees, but then seperately as a > customer. > > Robert > > "Jeff S" <A@B.COM> wrote in message > news:e28IWY4MGHA.668@TK2MSFTNGP11.phx.gbl... >> I'm modeling some entity that could reasonably be considered to be [one >> entity] or [two entities] --- people which are customers and employees. >> >> AFAIK, these are my three basic options: >> >> OPTION 1: >> Have one table, People, with some "flag column" that tells us which kind > of >> person we're dealing with. This one table would obviously have columns > that >> are relevant to only one person type (e.g., hire date is applicable to > only >> the employees). Such columns would always be NULL for rows of "the other" >> type (e.g., customers). >> >> OPTION 2: >> Have two tables - one for Customers and one for Employees. >> >> OPTION 3. >> Have three tables - one for People that has common attributes, and then > one >> each for Customers and Employees >> >> My Question: What are some general guidelines I should consider for > deciding >> which of these options to implement? Alternatively, which would YOU tend > to >> go with and why? Beyond general guidance I'm particularly interested in >> option 3; what are some of the pros and cons of having those 3 tables? >> >> Just looking for general guidance on this important decision I have to > make. >> >> Thank you for your time and consideration. >> >> -Jeff >> >> > >
Join. Join People to Customers to find people who are customers. Join
People to Employees to find people who are employees. Join Customers to Employees to find customers who are also employees and then join the result to People to obtain the common info. [quoted text, click to view] "Jeff S" <A@B.COM> wrote in message news:%23fK19r9MGHA.3728@tk2msftngp13.phx.gbl... > Okay, that makes sense... now, how do I go about "knowing" if a given > person is a Customer or Employee or both? It seems I'd have to query both > tables and get some count, perhaps using WHERE EXISTS. Is there a more > elegant way? > > Thanks > > > > > "Robert Bravery" <me@u.com> wrote in message > news:ucTJPp8MGHA.3936@TK2MSFTNGP12.phx.gbl... >> HI, >> I would go with SriSamp on this. Especially as an added complication >> where >> an employee can also be a customer, You wouldnt want to enter common >> things >> like name etc twice. By having two tables, employee and customer, with >> forein keys, you can look at him as an employees, but then seperately as >> a >> customer. >> >> Robert >> >> "Jeff S" <A@B.COM> wrote in message >> news:e28IWY4MGHA.668@TK2MSFTNGP11.phx.gbl... >>> I'm modeling some entity that could reasonably be considered to be [one >>> entity] or [two entities] --- people which are customers and employees. >>> >>> AFAIK, these are my three basic options: >>> >>> OPTION 1: >>> Have one table, People, with some "flag column" that tells us which kind >> of >>> person we're dealing with. This one table would obviously have columns >> that >>> are relevant to only one person type (e.g., hire date is applicable to >> only >>> the employees). Such columns would always be NULL for rows of "the >>> other" >>> type (e.g., customers). >>> >>> OPTION 2: >>> Have two tables - one for Customers and one for Employees. >>> >>> OPTION 3. >>> Have three tables - one for People that has common attributes, and then >> one >>> each for Customers and Employees >>> >>> My Question: What are some general guidelines I should consider for >> deciding >>> which of these options to implement? Alternatively, which would YOU tend >> to >>> go with and why? Beyond general guidance I'm particularly interested in >>> option 3; what are some of the pros and cons of having those 3 tables? >>> >>> Just looking for general guidance on this important decision I have to >> make. >>> >>> Thank you for your time and consideration. >>> >>> -Jeff >>> >>> >> >> > >
HI,
I would go with SriSamp on this. Especially as an added complication where an employee can also be a customer, You wouldnt want to enter common things like name etc twice. By having two tables, employee and customer, with forein keys, you can look at him as an employees, but then seperately as a customer. Robert [quoted text, click to view] "Jeff S" <A@B.COM> wrote in message news:e28IWY4MGHA.668@TK2MSFTNGP11.phx.gbl... > I'm modeling some entity that could reasonably be considered to be [one > entity] or [two entities] --- people which are customers and employees. > > AFAIK, these are my three basic options: > > OPTION 1: > Have one table, People, with some "flag column" that tells us which kind of > person we're dealing with. This one table would obviously have columns that > are relevant to only one person type (e.g., hire date is applicable to only > the employees). Such columns would always be NULL for rows of "the other" > type (e.g., customers). > > OPTION 2: > Have two tables - one for Customers and one for Employees. > > OPTION 3. > Have three tables - one for People that has common attributes, and then one > each for Customers and Employees > > My Question: What are some general guidelines I should consider for deciding > which of these options to implement? Alternatively, which would YOU tend to > go with and why? Beyond general guidance I'm particularly interested in > option 3; what are some of the pros and cons of having those 3 tables? > > Just looking for general guidance on this important decision I have to make. > > Thank you for your time and consideration. > > -Jeff > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |