Home Forums Database Design Design Ideas and Questions Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type RE: Pros and Cons - Table with loads of uniqe columns VS Row based keys with row data type

  • Personally I would stick to a relational model, so certainly not A2. But then I wouldn't like A1 either.

    The thing I would want to do is separate static from dynamic details. dob is static, and pay is not, so there are at leat two tables. name is not static either, it can change (for example on marriage), neither are employee bank account details (you need those unless you always pay in cash, not by money transfer in the banking system), but these (unlike pay) tend to change pretty rarely, and maybe it's useful to keep things that change very rarely separate from things that change often; but at the same time perhaps avoid keeping them with things that are totally static, since the dynamic things probably need associated history while the static things certainly don't. That makes me think that you have at least three tables, before you even look at the things that you might choose to split out to avoid nullable columns.

    Some of the dynamic things may not have a value yet, for example date of termination. This is where you have the possibility of a nullable column. These columns can be grouped into sets which whenever one column in a set has a value so do all the others in the same set (for example date of termination and reason for termination are a group, since either both have values or neither has). Each group could have its own table (with primary key employee id) which has rows only for non-null values. This is the classical way of avoiding null columns while retaining a relational structure. Alternatively, some groups could be bunched together so that some columns would be nullable. These two approaches to the groups may result in different storage requirements, so that needs to be looked at in case it turns out to be a critical difference. Whichever approach you adopt you have to cope with absent data; and there's not much difference in code difficulty between the two approaches, and no rule that says you have to take the same approach with each group.

    Some people get religious about this, and say "never have a nullable column". Others get religious and say "never force an outer join on yourself that you could avoid by having a nullable column". Both those groups of people are, in my view, utterly wrong.

    I too get religious, but what I say is "have a nullable column only when it's the right thing to do".

    Tom