Strong typing table design - SQL Server Forums

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)

Strong typing table design

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-05 : 12:32:51

Hi,

I have the following tables.

Pages
=====
pageID int pk

PagesAdverts
============
pageID int fk
advertID int fk

Adverts
=======
advertID int pk
advertTypeID int fk

AdvertTypes
===========
advertTypeID int pk

Fairly straight forward except that I only want *one* type of advert to go into 'PagesAdverts'. (and there's more than one type).

Its easy to check this upon insertion of the record into 'PagesAdverts' but I'm more concerned with a user modifying an adverts type. So should I just use a trigger to stop a user from modifying an advert's type after the advert itself is inserted?

Cheers,

XF.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-05 : 12:47:13

Do you only want one type of advert per page?

Why do you have a separate table.

And why would you want to prevent them from changing the original type?

Brett

8-)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-05 : 13:32:23

Hi,

Let me explain. There are three types of advert and a page is associated with all three. But for one of those types (sky scraper), a page can be associated with many instances. For the other two (Header and Footer) the page only has one of each.

So the Page table is really like this...

Pages
=====
pageID int pk
headerAdID int fk
footerAdID int fk

..and then the 'PagesAdverts' table holds the other type of adverts related to a page. Ultimately, the advert type determines where in the page its positioned so you see, if an advert of type header has been assigned to be a page's header ad, you don't want a user to come along and say, 'right, this advert is now a skyscraper advert, because it won't fit into the page layout properly.

So whilst avoiding having one table for each type of advert, I need to stop a user from modifying an advert's type.

Hmmm, I suppose I could check whether the ad is being referenced and only then allow its type to be changed.

XF.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources