Do 1 to 1 relations on db tables smell?

Question

I have a table that has a bunch of fields. The fields can be broken into logical groups - like a job's project manager info. The groupings themselves aren't really entity candidates as they don't and shouldn't have their own PKs.

For now, to group them, the fields have prefixes (PmFirstName for example) but I'm considering breaking them out into multiple tables with 1:1 relations on the main table.

Is there anything I should watch out for when I do this? Is this just a poor choice?

I can see that maybe my queries will get more complicated with all the extra joins but that can be mitigated with views right? If we're talking about a table with less than 100k records is this going to have a noticeable effect on performance?

Edit: I'll justify the non-entity candidate thoughts a little further. This information is entered by our user base. They don't know/care about each other. So its possible that the same user will submit the same "projectManager name" or whatever which, at this point, wouldn't be violating any constraint. Its for us to determine later on down the pipeline if we wanna correlate entries from separate users. If I were to give these things their own key they would grow at the same rate the main table grows - since they are essentially part of the same entity. At no pt is a user picking from a list of available "project managers".

So, given the above, I don't think they are entities. But maybe not - if you have further thoughts plz post.

Answer 1

To me, they smell unless for some rows or queries you won't be interested in the extra columns. e.g. if for a large portion of your queries you are not selecting the PmFirstName columns, or if for a large subset of rows those columns are NULL.

I like the smells tag.

Answer 2

Why do you feel that the group of fields are not an entity candidates? If they are not then why try to identify them with a prefix?

Either drop the prefixes or extract them into their own table.

Answer 3

I don't usually use 1 to 1 relations unless there is a specific performance reason for it. For example storing an infrequently used large text or BLOB type field in a separate table.

I would suspect that there is something else going on here though. In the example you give - PmFirstName - it seems like maybe there should be a single pm_id relating to a "ProjectManagers" or "Employees" table. Are you sure none of those groupings are really entity candidates?

Answer 4

I use 1 to 1 relationships for inheritance-like constructs.

For example, all bonds have some basic information like CUSIP, Coupon, DatedDate, and MaturityDate. This all goes in the main table.

Now each type of bond (Treasury, Corporate, Muni, Agency, etc.) also has its own set of columns unique to it.

In the past we would just have one incredibly wide table with all that information. Now we break out the type-specific info into separate tables, which gives us much better performance.

For now, to group them, the fields have prefixes (PmFirstName for example) but I'm considering breaking them out into multiple tables with 1:1 relations on the main table.

Create a person table, every database needs this. Then in your project table have a column called PMKey which points to the person table.

Answer 5

It is valuable splitting them up into separate tables if they are separate logical entities that could be used elsewhere.

So a "Project Manager" could be 1:1 with all the projects currently, but it makes sense that later you might want to be able to have a Project Manager have more than one project. So having the extra table is good.

If you have a PrimaryFirstName,PrimaryLastName,PrimaryPhone, SecondaryFirstName,SecondaryLastName,SEcondaryPhone

You could just have a "Person" table with FirstName, LastName, Phone

Then your original Table only needs "PrimaryId" and "SecondaryId" columns to replace the 6 columns you previously had.

Also, using SQL you can split up filegroups and tables across physical locations. So you could have a POST table, and a COMMENT Table, that have a 1:1 relationship, but the COMMENT table is located on a different filegroup, and on a different physical drive with more memory.

1:1 does not always smell. Unless it has no purpose.