Splitting a table based on business logic

Question

This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach.

Typically, I break database design into two categories, Business Logic and Recorded Data. Business Logic are those things which bring meaning to the Recorded Data. Eg, PartSerialNumber has meaning because we have parts with serial numbers.

So I'd create a structure like...

CREATE TABLE PartsTable
{
    PartID INT, -- NOT NULL IDENTITY PK...
    PartSerialNumber VARCHAR(20), -- NOT NULL
    CreateDate DATE, -- NOT NULL, maybe default
    -- other stuff related to the specific part
}

 -- this is a business logic table, the codes have meaning to the business
CREATE TABLE ErrorCodes
{
    ErrorCode VARCHAR(5), -- NOT NULL, PK
    ErrorDescription VARCHAR(MAX),
    -- OTHER STUFF FOR THE ERROR CODES
}

Then we have a logging system to catch errors,

CREATE TABLE PartErrors
{
    PartID INT , -- NOT NULL FK to PartsTable
    ErrorCode VARCHAR(5) -- NOT NULL, SEARCHED ON, FK to Business logic table
    ErrorDate DATE -- NOT NULL, when the error occurred, SEARCHED ON
    -- other search terms
}

Now, there is additional information about each error, but these are not searched on; maybe something like the clock frequency of the part when the error occurred.

So then I'd create another table

CREATE TABLE PartErrorsData
{
    PartID INT, -- not null, fk to PartsErrors
    ErrorCode VARCHAR(5), -- NOT NULL FK TO PARTSERRORS
    ErrorDate DATE, -- not null FK to PartsErrors
    ClockFrequencyHz INT, -- this may be null if the logger could not read the freq
    MachineNumber INT -- may be null if the logger cannot comm with the machine
    -- maybe other stuff, again, nothing that is searched upon, nor in the BL
}

So the question: Is there any merit in separating these tables or should I have simply put all the columns from the PartErrorsData table into the PartsErrors table?

It seems like we can somewhat accomplish this with an additional index on the fields which are searched. I'm somewhat torn between using an index on the searching fields and breaking the search fields from the data fields.

It is important to keep in mind that the fields on the search table are business logic fields; they relate to a table containing BL. The Data table is pretty free form and is simply recording values from feedback files.

I personally like this approach as it clearly defines how the BL relates to the data so that we can join additional tables if necessary. For instance, if there is another system which has Error related data, we can join it to the PartErrors table without having to worry about the data, just the BL.

EDIT: Updated the tables so that the compound key is unique. This was a mistake with the original question. Of course it would have to have all the data required to correlate to an instance of the error.

Another note in response to the first answer. We have [at least] 3 tables of different error data.

Some parts may provide clock freq, and machine id. Other parts may report temperature surrounding the part, voltage coming into the part, etc.

So, using one table would have NULLs in all fields which the part does not report.

Regarding data which has no meaning to business, a clock frequency does not have meaning without the part. A value recorded by the recording mechanism could have errors. Maybe we recorded the frequency as 100Hz, but it was supposed to be 100 MHz. The VALUE 100 does not have meaning until we bring it to light with the BL. This part has a clock which is measured in MHz.

However, the value 00-139-228-AA has meaning to the business as a part number so that an incorrectly recorded part number &&^%% would be noticed immediately as it does not correlate back to the BL. The mistaken value is not a catch-able error.

Answer 1

I cannot imagine any data that does not have a value to the business. If it doesn't have value why store it? Even technical logs have business value ("If we don't have these logs it is 50% more expensive to fix bug").

There are a few valid reasons to split a table:

• Partition hot and cold data for cache efficiency
• Too many columns (>1000 or so)
• Many columns almost always null

Not sure what you gain by splitting off data by layering concerns, though. Everything gets harder to understand and to maintain (not easier).

My recommendation clearly is to have only one table. Don't artificially split the data.