keeping tables in sync/design issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

keeping tables in sync/design issue

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2002-06-27 : 13:37:33

Hi I'm having trouble figuring out how to keep a group of records in sync I have a user’s table tblUser which contains a list of all users. There are two columns that contain expiration dates of our two product groups. There is also an accounts table that lists all of the products for a particular customer. This table also has an expiration column. Whenever the user table is modified there is a trigger that fires and updates the expiration column in the accounts table. My question is what is the best way to update this expiration date when a new account is inserted? Should there be a trigger which gets the expiration date for the particular user? If the product is already expired, I won’t allow the insert.

Having the expiration date in the accounts table solves a number of different business rule problems.

I’m just curious what is the best way to keep the tables in sync? Or is this a really bad design. (I’m sure there is a better way) Essentially users have a number of different products, but in reality the product list doesn’t really matter the main thing is which groups the products are in. The relationship between the product group and the user is the important aspect. (kinda crazy but that is how the company works.) Also I realize “what happens if there is another group?” question. They are pretty adamant that there won’t be.

create table tblUser (
us_key int NOT NULL,
us_username varchar(30) NOT NULL,
us_cars_expire datetime,
us_plants_expire datetime
)
go
create table tblGroup (
grp_key int NOT NULL,
grp_name varchar(30) NOT NULL
)
go
create table tblProduct(
prod_key int NOT NULL,
grp_key int NOT NULL,
prod_name varchar(30) NULL
)
go
create table tblAccount(
us_key int NOT NULL,
prod_key int NOT NULL,
account_expired datetime NULL
)
go
insert into tblUser values (1,'user1',null,null)
go
insert into tblUser values (2,'user2',null,null)
go
insert into tblUser values (3,'user3',null,null)
go
insert into tblUser values (4,'user4',null,null)
go
insert into tblGroup values (1,'cars')
go
insert into tblGroup values (2,'plants')
go
insert into tblProduct values (1,1,'honda')
go
insert into tblProduct values (2,1,'ford')
go
insert into tblProduct values (3,1,'subaru')
go
insert into tblProduct values (4,2,'flowers')
go
insert into tblProduct values (5,2,'bushes')
go
insert into tblAccount values (1,1,null)
go
insert into tblAccount values (1,2,null)
go
insert into tblAccount values (1,3,null)
go
insert into tblAccount values (1,5,null)
go
insert into tblAccount values (2,4,null)
go
insert into tblAccount values (2,5,null)
go

Nic

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-28 : 08:21:42

Many reads, no responses here indicates you've got an interesting issue (I certainly think so anyway) but not sufficient detail. 'Best practices' questions such as yours require more background to afford the enterprise architects here a reasonable picture of your environment. I would start by clearly describing the business rules you're implementing, vis-a-vis account expiration.

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources