Database Normalization: Related, but categorically distinct, information.

[pbmods]

Heya.

I'm adding features to a pre-existing project, and I was curious about the way I've been going about designing my database additions.

My methodology is to keep my stuff separate from the other guy's stuff as much as possible. That way, I know that if it breaks, it is definitely my code's fault (or at least, 85% of the time it is... who HIRES these people?!).

Likewise, on the database side, there's a pre-existing CMS in there, and I want to add additional fields.

Now, I can be reasonably sure that if I add columns to the CMS table, the other guy's CMS code will break with 'column count doesn't match value count' errors. And even if I wanted to fix that (which I don't), I'd have to start mucking around in this CMS to make sure that editing entries doesn't null out my custom fields, etc.

So I created a separate table to map a one-to-one relationship.

In pseudo-sql, we have now two tables:

table `CMS`
(
    `CmsID`    serial,
    `Data`    text
    .
    .
    .
    (and so on, pretty much your everyday sub-standard CMS table goes here)
)
 
table `CMS_Geocodes`
(
    `CmsID`    serial,
    `Lat`    float(8,5),
    `Long`    float(8,5),
)
 

Ok, so obviously, this is the only feasible way to do it without having to maintain the other guy's code.

But my question is in regarding to *my* projects. Should I be splitting up my data this way even when I'm maintaining the backend, or does that create too much overhead? How does this fit in with the database normalization model?

For example, suppose I had a table that looked like this:

table `Util_Users`
(
    `Username`    varchar(60) primary key,
    `Password`    char(40),
    `created`    timestamp,
    `realname`    varchar(200),
    `dob`        date,
    `bio`        text
)
 

Would that be considered an acceptible setup, or should I do this instead:

table `Util_Users`
(
    `Username`    varchar(60) primary key,
    `Password`    char(40),
)
 
table `Util_UserProfiles`
(
    `Username`    varchar(60) primary key,
    `created`    timestamp,
    `realname`    varchar(200),
    `dob`        date,
    `bio`        text
)
 

In both cases, there is a one-to-one relationship between the primary key and its corresponding data. But in the second example, categorically distinct data is separated into its own table.

What are your thoughts on this? Should I strive to keep as much (related) data in as few tables as possible, or should I be separating stuff based on its purpose?

[MMcCarthy]

In both cases, there is a one-to-one relationship between the primary key and its corresponding data. But in the second example, categorically distinct data is separated into its own table.

What are your thoughts on this? Should I strive to keep as much (related) data in as few tables as possible, or should I be separating stuff based on its purpose?

Essentially columns dependent on the primary key belong in the same table and only columns dependent on the primary key. However, from a practical point of view it is sometimes appropriate to break out tables into one to one relationships. However, I would create a new primary key and have foreign key reference to the primary key of the first table.