One on One table relation - is it harmful to keep relation in both tables?

Question

I have 2 tables that their rows have one on one relation.. For you to understand the situation, suppose there is one table with user informations and there is another table that contains a very specific informations and each user can only link to one these specific kind of informations ( suppose second table as characters )

And that character can only assign to the user who grabs it, Is it against the rules of designing clean databases to hold the relation key in both tables?

User Table: user_id, name, age, character_id

Character Table: character_id, shape, user_id

I have to do it for performance, how do you think about it?

Answer 1

Yes, I'd say it was against the rules of designing clean databases, as the data you have is repeated, therefore you have to actively maintain two sets of data at all times for fear they are out of sync. If you don't do this, you can't trust the data. :)

As a side note: You say you're doing this for performance reasons? What benefits have you seen of doing this? As regardless of which table you access the data from, you should find (with proper indexes) that there is very little difference? It might be you are fixing the wrong issue here and maybe there are other things to look at first (like indexes, improving your queries etc).

Edit: Selecting free characters should be easy enough as you would get rid of the character_id from the users table and only have user_id in the characters one:

Users:  user_id, name, age
Characters:  character_id, shape, user_id

Then you would be able to do some queries like these:

//something like this to query for free characters.
SELECT * FROM characters WHERE user_id IS NULL;

// To access a list of a users characters, you could do something like this:
SELECT * FROM characters WHERE user_id = 42;

// More information about the user, while still grabbing character info.
SELECT * FROM characters AS c INNER JOIN users AS u ON u.user_id = c.user_id WHERE u.user_id = 42;

Or is there more about your model that I'm maybe missing with this? (As I don't pretend to know your database layout ;) ). I'm assuming a one-to-many relationship, as it does appear to be that way (one user can have many characters).

Answer 2

In a 1:1 relationship there still should be a record that would be considered the "parent".

In this case, I would consider user to be hierarchically above character, so only use the foreign key in the character table.

Answer 3

Couple of reasons:

• Redundant data
• Need to keep both the tables always updated with the correct keys...

I would look at the reasons for the performance problems since this is a 1:1 relation, you will get the single record pretty quickly when you join on the PK:FK

Answer 4

It is certainly possible to split data like this for performance reasons, particularly if you are working with lots of data in lots of columns: you may for example just put "basic" user info in one table and detailed stuff (eg. with large text fields) in another because these are not accessed as often as the basic things. Maybe you need full text capabilities on parts of the data so you use tables with different storage engines. If ebaghaki needs to do it for performance reasons, i'd say its ok - be sure though, that you app ensures the validity of the keys (or use triggers to keep things up to date?).

An alternative would be to build a lookup table which just holds references to the user and character tables. Then you wouldn't have any problems with null values for users that haven't chosen a character or characters that no users have chosen. This is probably your best bet because you aren't splitting the table for any of the reasons mentioned above.