When I left a few weeks ago I was examining the Composite Key versus Surrogate Key relationships. Today I’d like to put some meat on that bone and let everyone see (I’m a visual learner too) what the structures might look like in SQL Management Studio diagram designer.
Here is a simple Product and Manufacturer model. Each Product has an ID, a surrogate key that identifies this element of data (or domain / row) and similarly each Manufacturer as well. The combination of the two, ProductManufacturers brings those together in a way that allows for uniqueness to be enforced via an Index and continues to allow for rapid additions to the table. Some more rather simple things could be added to denote expired products and even something that refers back to the previous edition, but the base supports those additions without altering the main design function.
The next thing to note is that queries become very simple against these types of structures. The cross reference is naturally built and easy to make without resorting to a Cartesian product or some other multiplicative cross of data.
If you recall from the last post, I was trying to illustrate the difference between making the Product Id and Manufacturer Id the Primary Key and Clustered Key. The example above would illustrate that well if the ProductManufacturerId column was removed and that actually occurred. Consider a given Manufacturer and the instance in which that Manufacturer was adding to the line of products your business stocked or sold. The groupings of product would alter the data as it’s sorted on disk, provided the PK was Clustered (and it should be). The result would be disorganized data on the disk and eventually the performance of queries against that table would slow. That slowness over time is a common problem in applications, and the root typically lies in the queries that are driving the applications.
Good Designs to you.
Data Chef out,..