This question and thus my answer may be close to the gray line of subjective, but at the least I think it would be common practice to separate out the ‘admin’ tables into their own db for what it sounds like you’re doing. If you can tie a client to a specific server and db … Read more
Here is one way to do it: Essentially, we never modify or delete the existing data. We “modify” it by creating a new version. We “delete” it by setting the DELETED flag. For example: If product changes the price, we insert a new row into PRODUCT_VERSION while old orders are kept connected to the old … Read more
According to The Queen Of Indexing – Kimberly Tripp – what she looks for in a clustered index is primarily: Unique Narrow Static And if you can also guarantee: Ever-increasing pattern then you’re pretty close to having your ideal clustering key! Check out her entire blog post here, and another really interesting one about clustering … Read more
The models you linked fail to address some major properties BOMs normally have: Parts and sub-assemblies can be reused. For example, it is common for a same kind of bolt to be used in many assemblies. There needs to be a BOM-specific quantity. For example, it’s important to know that one assembly needs (say) 50 … Read more
Here you go. An illustrative story: http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
In general I prefer obtion “B” (i.e. one table for base class and one table for each “concrete” subclass). Of course this has a couple of drawbacks: first of all you have to join at least 2 tables whenever you have to read a full instance of a subclass. Also, the “base” table will be … Read more
Revised 01 Jan 11 Ok, so there is a gap between where I sit (deliver fully auditable databases; yours being a particular requirement of that) and where you sit: based on your questions and comments. Which we will probably work out in the commentary. Here’s a position to start from. To provide this requirement, there … Read more
In my personal opinion you should avoid composite primary keys due to several reasons: Future changes: when you design a database you sometimes miss what in the future will become important. A significant example for this is thinking a combination of two or more fields is unique (and thus can become a primary key), whereas … Read more
MyISAM is transactionless and heap-organized. The records are identified by the row offset in the table and the indexes store this offset as a row pointer. InnoDB supports transactions and is index-organized. The records are identified by the value of the PRIMARY KEY (or a hidden internal column is there is no PRIMARY KEY defined) … Read more
A 128-bit GUID (uniqueidentifier) key is of course 4x larger than a 32-bit int key. However, there are a few key advantages: No “IDENTITY INSERT” issue when merging content If you use a COMB value instead of NEWSEQUENTIALID(), you get a “free” INSERT timestamp. You can even SELECT from the primary key based on a … Read more