How do I decide whether columns should be combined in a single table or split into multiple tables?

Question

should we always combine one-to-one tables?

I have a user id uid, a table containing the basics:

uid, name, address, etc..

and another table that is essentially the same thing, with the foreign key on the previous user table

uid, stats, etc..

should i just combined them? when does it make sense to and when would i not want to?

Answer 1

It makes sense to keep these tables separate when it will be very common that the fields in the second table will not be filled out, therefore having the second table allows you to save that much memory per row without that information filled.

In your example, if only a few users will have 'stats, etc', then it may be a good idea to keep that information in a separate table. Don't even create a row for those users without that information.

Answer 2

Check out this resource on the Five Normal Forms for Relational Databases. In it you will find some guidelines for deciding how to combine information or split it out into tables.

In a nutshell, use this rule of thumb:

• Each table should represent one type of thing.
• The primary key should uniquely distinguish that thing from other items in the table.
• Every column in the table should depend on the primary key value and only the primary key value.
  • If multiple fields in a single table form a logical sub-group (for example parts of an address), then they probably deserve their own table.

Answer 3

One-to-one relationship is a thing defined by ER model.

This can be mapped into a relational model in several ways, most widely used of them being:

• Creating one table with attributes of both entities. Useful for strict 1:1 relationships.

• Creating one table per entity and referencing them in an additional link table with UNIQUE constraints on both link fields. Useful for (0-1):(0-1) relationships and for adding attributes to the relationships themselves.

• Creating one table per entity and referencing one table from another one using a FOREIGN KEY constraint on a UNIQUE field. Useful for 1:(0-1) relationships.

Performance issues can influence the choice of the relational representation of this relationship as well.

You may want to read this article in my blog:

• What is entity-relationship model?