This might force a bit of change for your tables, but we do something like this at work:
Ensure each table has a int/guid primary key of some sort:
Organization Table
ID | Name
1 | MyOrganization
Then create a type-table to house your different LeadSource types:
LeadSourceTypeKey | Description
1 | Organization
2 | Company Office
Then on your ProductSoldList Table add 2 columns LeadSourceID
and LeadSourceType
Then you can easily query the ProductSoldList
TABLE and know which LeadSource to join back to:
SELECT *
FROM [ProductSoldList] PL
INNER JOIN [Organization] O
ON PL.LeadSourceID = O.ID AND
PL.LeadSourceType = 1
INNER JOIN [Company Office] CO
ON PL.LeadSourceID = CO.ID AND
PL.LeadSourceType = 2
I agree with Maess - if you have distinct entities, each of those ought to have their own ID columns. But I have a strong objections against using GUID's for your primary keys - or more specifically - for your SQL Server's clustering keys.
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
You really need to keep two issues apart:
1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.
2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.
As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.
Yes, I know - there's newsequentialid()
in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.
Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
Quick calculation - using INT vs. GUID as Primary and Clustering Key:
- Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
- 6 nonclustered indexes (22.89 MB vs. 91.55 MB)
TOTAL: 25 MB vs. 106 MB - and that's just on a single table!
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.
Best Answer
To say that the use of
"Composite keys as PRIMARY KEY is bad practice"
is utter nonsense!Composite
PRIMARY KEY
s are often a very "good thing" and the only way to model natural situations that occur in everyday life! Having said that, there would also be many situations where using a composite PK would be cumbersome and unwieldy and therefore not an optimal choice.Your question is:
"if composite primary keys are bad practice...
(answered)and if not, in which scenarios is their use beneficial?"
.Below is an example of where composite keys represent a rational/beneficial choice as the PK (indeed the only rational one as I see it - in the fiddle here, there's an extra example of having grades also!
On the plus side for composite keys, think of the classic Databases-101 teaching example of students and courses and the many courses taken by many students!
Create tables course and student:
I'll give you the example in the PostgreSQL dialect (and MySQL) - should work for any server with a bit of tweaking.
Now, you obviously want to keep track of which student is taking which course - so you have what's called a
joining table
(also calledlinking
,bridging
,many-to-many
orm-to-n
tables). They are also known asassociative entities
in more technical jargon!1 course can have many students.
1 student can take many courses.
So, you create a joining table
Now, the only way to sensibly give the
registration
table aPRIMARY KEY
is to make thatKEY
a combination of course and student. That way, you can't get:a duplicate of student and course combination
a course can only have the same student enrolled once, and
a student can only enroll in the same course one time only
you also have a ready made search
KEY
on course per student - AKA a covering index,it is trivial to find courses without students and students who are taking no courses!
-- The db-fiddle example has the PK constraint folded into the
CREATE TABLE
-- It can be done either way. I prefer to have everything in theCREATE TABLE
statement.Now, you could, if you were finding that searches for student by course were slow, use a
UNIQUE INDEX
on (sc_student_id, sc_course_id).There is no silver bullet for adding indexes - they will make
INSERT
s andUPDATE
s slower, but at the great benefit of greatly decreasingSELECT
times! It's up to the developer to decide to index given their knowledge and experience, but to say that compositePRIMARY KEY
s are always bad is just plain wrong.In the case of joining tables, they are usually the only
PRIMARY KEY
that make sense! Joining tables are also very frequently the only way of modelling what happens in business or nature or in virtually every sphere I can think of!This PK is also of use as a
covering index
which can help speed up searches. In this case, it would be particularly useful if one were searching regularly on (course_id, student_id) which, one would imagine, can often be the case!This is just a small example of where a composite
PRIMARY KEY
can be a very good idea, and the only sane way to model reality! Off the top of my head, I can think of many many more.An example from my own work!
Consider a flight table containing a flight_id, a list of departure and arrival airports and the relevant times and then also a cabin_crew table with crew members!
The only sane way this can be modelled is to have a flight_crew table with the flight_id and the crew_id as attibutes and the only sane
PRIMARY KEY
is to use the composite key of the two fields!