|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
design questiononly client info, but what type of program they are eligible for. I have narrowed it down to 2 options, but was really looking for opinions. The current design is like this: Client Table: ClientId LName FName (Lots of other info that I will look at later) IsCash(bit) IsFoodStamps(bit) IsMedical(bit) But I'd like to split it out a bit so the various programs will be in another table(s): Option 1: CLIENT TABLE: ClientId LName FName PROGRAMS TABLE: ProgramId ProgramName CLIENT PROGRAM LOOKUP TABLE: ClientId ProgramId Option 2: CLIENT TABLE: ClientId LName FName CLIENT PROGRAM TABLE: ClientId Program (with a CHECK constraint so that only certain values can be entered) Other ideas are welcomed as well. TIA, ChrisR
Show quote
> Howdy all. I have developers wanting to design a table that will hold not I'm leaning toward your option 1, or this, with 1 Program = many Clients:> only client info, but what type of program they are eligible for. I have > narrowed it down to 2 options, but was really looking for opinions. The > current design is like this: > > Client Table: > > ClientId > LName > FName > (Lots of other info that I will look at later) > IsCash(bit) > IsFoodStamps(bit) > IsMedical(bit) Program table: ProgramID Description Client Table: ClientID LName FName ProgramID I think your decision should be based on whether or not a client can belong to one program at a time, or more than one at a time. So if I can belong to both Cash and FoodStamps programs, this design won't work and you will want to go with your Option 1. -- Peace & happy computing, Mike Labosh, MCSD MCT Owner, vbSensei.Com "Escriba coda ergo sum." -- vbSensei To answer your question "yes, a client can and will belong to more than one
program". But why wouldn't Option 2 work? CLIENT TABLE: ClientId LName FName CLIENT PROGRAM TABLE: ClientId Program Insert into Client(1234,'Blow','Joe') Insert into ClientProgram(1234, 'FoodStamps') Insert into ClientProgram(1234, 'Medical') I know it's probably not the proper way to do things, but would reduce the amount of joins. Plus if I had a CHECK constraint on the column "program", only the right info could be entered. Thoughts? Show quote "Mike Labosh" <mlabosh_at_hotmail.com> wrote in message news:OrAyv0gSGHA.4608@tk2msftngp13.phx.gbl... > > Howdy all. I have developers wanting to design a table that will hold not > > only client info, but what type of program they are eligible for. I have > > narrowed it down to 2 options, but was really looking for opinions. The > > current design is like this: > > > > Client Table: > > > > ClientId > > LName > > FName > > (Lots of other info that I will look at later) > > IsCash(bit) > > IsFoodStamps(bit) > > IsMedical(bit) > > I'm leaning toward your option 1, or this, with 1 Program = many Clients: > > Program table: > ProgramID > Description > > Client Table: > ClientID > LName > FName > ProgramID > > I think your decision should be based on whether or not a client can belong > to one program at a time, or more than one at a time. So if I can belong to > both Cash and FoodStamps programs, this design won't work and you will want > to go with your Option 1. > -- > > > Peace & happy computing, > > Mike Labosh, MCSD MCT > Owner, vbSensei.Com > > "Escriba coda ergo sum." -- vbSensei > > ChrisR wrote:
Show quote > To answer your question "yes, a client can and will belong to more than one Two things to consider. Under option 2 the users won't be able to add> program". But why wouldn't Option 2 work? > > CLIENT TABLE: > ClientId > LName > FName > > CLIENT PROGRAM TABLE: > ClientId > Program > > > Insert into Client(1234,'Blow','Joe') > > Insert into ClientProgram(1234, 'FoodStamps') > Insert into ClientProgram(1234, 'Medical') > > I know it's probably not the proper way to do things, but would reduce the > amount of joins. Plus if I had a CHECK constraint on the column "program", > only the right info could be entered. > > Thoughts? > new Programs - that would require a schema change to alter the CHECK constraint. Also, if you want to change the program description for any reason then you'll have to change it on every row, not just once. Perhaps option 2 will also require more storage - it appears so from your sample data. In general I'd say that Option 1 is better for "descriptive" text (because business users are more likely to want changes to descriptions), whereas Option 2 is better for sets of codes because they tend to change less frequently. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- "ChrisR" <Chr***@noEmail.com> wrote in message It would work.news:eMm9AAhSGHA.4740@TK2MSFTNGP14.phx.gbl... > To answer your question "yes, a client can and will belong to more than > one > program". But why wouldn't Option 2 work? Option one (clients, programs, and a third table to match the two) is the standard method for representing a many to many relationship. Option two would work if the only information about the program that you care about is the program name. If you plan on entering any other details about the program, then this isn't good enough. I hadn't thought of either of the points you guys vrought up. Thanks a lot
and have a great weekend. Show quote "ChrisR" <Chr***@noEmail.com> wrote in message news:eMm9AAhSGHA.4740@TK2MSFTNGP14.phx.gbl... > To answer your question "yes, a client can and will belong to more than one > program". But why wouldn't Option 2 work? > > CLIENT TABLE: > ClientId > LName > FName > > CLIENT PROGRAM TABLE: > ClientId > Program > > > Insert into Client(1234,'Blow','Joe') > > Insert into ClientProgram(1234, 'FoodStamps') > Insert into ClientProgram(1234, 'Medical') > > I know it's probably not the proper way to do things, but would reduce the > amount of joins. Plus if I had a CHECK constraint on the column "program", > only the right info could be entered. > > Thoughts? > > > > "Mike Labosh" <mlabosh_at_hotmail.com> wrote in message > news:OrAyv0gSGHA.4608@tk2msftngp13.phx.gbl... > > > Howdy all. I have developers wanting to design a table that will hold > not > > > only client info, but what type of program they are eligible for. I have > > > narrowed it down to 2 options, but was really looking for opinions. The > > > current design is like this: > > > > > > Client Table: > > > > > > ClientId > > > LName > > > FName > > > (Lots of other info that I will look at later) > > > IsCash(bit) > > > IsFoodStamps(bit) > > > IsMedical(bit) > > > > I'm leaning toward your option 1, or this, with 1 Program = many Clients: > > > > Program table: > > ProgramID > > Description > > > > Client Table: > > ClientID > > LName > > FName > > ProgramID > > > > I think your decision should be based on whether or not a client can > belong > > to one program at a time, or more than one at a time. So if I can belong > to > > both Cash and FoodStamps programs, this design won't work and you will > want > > to go with your Option 1. > > -- > > > > > > Peace & happy computing, > > > > Mike Labosh, MCSD MCT > > Owner, vbSensei.Com > > > > "Escriba coda ergo sum." -- vbSensei > > > > > >  |
|||||||||||||||||||||||
Other interesting topics