Composite Primary Key

[vidru]

I'm trying to avoid creating a new table. Here's the scenario:

I have an existing table with the following structure...

CREATE TABLE [dbo].[SpPrices] (
	[DoctorID] [varchar] (20) NOT NULL ,
	[ProductID] [IDSTR] NOT NULL ,
	[Price] [MONEY] NULL,
	[FacilityID] [IDSTR] NOT NULL,
) ON [PRIMARY]

The primary key for this table is a composite key:

ALTER TABLE [dbo].[SpPrices] ADD CONSTRAINT [SpPrices_DoctorProductFacility] PRIMARY KEY CLUSTERED 
	(
		[DoctorID],
		[ProductID],
		[FacilityID]
	)  ON [PRIMARY]

IDSTR is a user-defined type (varchar(20)).

I have a requirement to give the ability to set special pricing on a higher level than DoctorID (practice groups).

If I add my PracticeGroupID (it's an INT) field to this table, both it and DoctorID would have to be nullable - no problem, except that I won't be able to use either of these fields in a composite primary key anymore.

I can add a CHECK constraint to enforce that records have either a DoctorID or a PracticeGroupID, but I'm trying to find a way to keep duplicate records out from the database side as opposed to the front end.

The only thing I can think of would be a Type field (likely and Integer, set to 0 or 1) to delineate between special pricing for a doctor or a practice group. Then my composite key would look like:

ALTER TABLE [dbo].[SpPrices] ADD CONSTRAINT [SpPrices_ProductFacilityType] PRIMARY KEY CLUSTERED 
	(
		[ProductID],
		[FacilityID],
		[Type]
	)  ON [PRIMARY]

I guess this would work, but I'd like to get some other opinions.

Thanks in advance for any suggestions.

-dave

 

[Catadmin]

Other options?

If your subject is a Doctor, make the PracticeID = DoctorID. If your subject is a Practice, make the DoctorID = PracticeID. Have a bit flag on your Doctor table which says DoctorOnly and if it is false, the record is a Practice's record, not a Doctor's record.

It saves you adding a table, unless you already have the table added with data.

I'll see if I can come up with any other thoughts on this too.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[rac2]

Seems reasonable. Or, what about a default value, say 1 meaning not pricable at the group level, instead of allowing PracticeGroupID to be nullable. Then it could be part of the composite key.

Im not familiar with the performance implications of this idea. If half of the rows have PracticeGroupID = 1, would that be a problem?

There is an outline of considerations in Books Online topic Clustered Indexes Overview.

 

[Catadmin]

I like the idea that PracticeGroupID would default to 1 or have another value if it was a group, but not be nullable. Then you could add it to the composite key. The problem is you'd have to remove DoctorID from the composite key if DoctorID is going to be NULL when PGID is other than 1. So, maybe you need two default values. Have PracticeGroupID = 1 as a default and have DoctorID = 2 as a (this is a Practice) default.

The performance implications aren't that bad unless your hardware is awful or your queries are badly written. We have composite keys of up to 7 columns on some few (2 or 3) of our tables. However, be aware, the bigger the key and the bigger the table and the more often you use composite keys in your database, the more performance issues you will have. Composite keys should be used sparingly and at need. Otherwise, I advise going with Identity columns as your PK if no other natural key suggests itself.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"