|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
1, 2, or 3 Tables (Data Modeling Question)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. -- Show quoteHTH, SriSamp Email: sris***@gmail.com Blog: http://blogs.sqlxml.org/srinivassampath URL: http://www32.brinkster.com/srisamp "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. Show quote "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 Show quote "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 Show quote "Robert Bravery" <m*@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. Show quote "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" <m*@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 >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics