design question

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)
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> 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|||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...
not
> 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:
> To answer your question "yes, a client can and will belong to more than on
e
> 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?
>
Two things to consider. Under option 2 the users won't be able to add
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" <ChrisR@.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?
It would 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 wend.
"ChrisR" <ChrisR@.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...
> not
have
The
Clients:
> belong
belong
> to
> want
>