design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
...
...
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
...
...
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
....
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David
|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> ...
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>
|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.4ax.com... [vbcol=seagreen]
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
many
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)