Design Question - 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)

Design Question

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-05-30 : 15:38:01

Lets say I have two Tables, Widgets and PromoCodes. A widget can have zero or one promo code.

What's the best way to impliment such a rule?
Have a "blank / null record" in PromoCode and do this:


ALTER TABLE [dbo].[Widget] ADD 
	CONSTRAINT [FK_Conference_PromoCode] FOREIGN KEY 
	(
		[PromoCode]
	) REFERENCES [dbo].[PromoCode] (
		[PromoCode]
	)

Or should I "Allow Nulls" on the field in the Widget table and impliment it like this:


ALTER TABLE [dbo].[widget] ADD 
	CONSTRAINT [FK_Widget_PromoCode] FOREIGN KEY 
	(
		[PromoCode]
	) REFERENCES [dbo].[PromoCode] (
		[PromoCode]
	) NOT FOR REPLICATION 

AlTER TABLE [dbo].[Widget] NOCHECK CONSTRAINT [FK_Widget_PromoCode]

I'm thinking the second way is best. Is that the way to impliment this? Or do I impliement a many-to-many type join?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 16:03:44

I think that if a promo code is an attribute of a widget and can have 1 or not, a nullable promo code in widget with a FK to a list of valid promo codes in promo should be it....

Am I missing some complexity about this?

Brett

8-)

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-05-30 : 16:57:01

So the second set of code should do that right Brett?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources