CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
user1992
user1992

Reputation: 47

Database design: One to one relationship from a single table?

For a school subject I have to make a small application with different type of relationships: One to one, one to many, many to many.

If for example a relationship status (married with/in a relationship with) between 2 user ID's from the same table 'user' still a one to one relationship?

For example:

Table 1: 'user'

user_id (PK) | username | password | date_registered

Table 2: 'relationship'

user_one_id (FK) | user_two_id (FK) | status

Is this a right example of a one to one database or does it per se have to be a connection between 2 tables with an external table which connects them?

Upvotes: 1

Views: 4201

Answers (2)

SandPiper
SandPiper

Reputation: 2906

If by "an external table connecting them" you mean a third table, then no, that is not necessary.

Your relation table could use some work though. You don't want to have two fields with user ids. That will cause you headaches later. I would recommend setting those tables up like this:

USER

user_id (pk)
username
password        --Potential security problems, but for academic purposes, sure.
date_registered
family_unit_id


RELATIONSHIP

family_unit_id (fk)
user_id (fk)
spouse_type --(e.g. husband, wife, etc.)

Now you can get your many to many relationship by doing this:

SELECT r.family_unit_id, r.user_id, u.username
FROM RELATION r LEFT JOIN USER u ON r.user_id = u.user_id

Your one to one relationship is going to be something that is unique to the same record between tables. In the case you described, user_id (husband) related to user_id (wife) is not a one to one relationship. With what you have given, you can make a one to one relationship by joining subsets of the data, such as this example.

SELECT r1.family_unit_id, r1.username as HusbandName, r2.username as WifeName
FROM (SELECT * FROM RELATION WHERE spouse_type = 'Husband') r1
LEFT JOIN (SELECT * FROM RELATION WHERE spouse_type = 'Wife') r2
     ON r2.family_unit_id = r1.family_unit_id

The above query essentially gets two tables with a unique primary key of family_unit_ids and the userid associated with either husband or wife. Then, you join them together. Because we are assuming that there will never be more than two spouses in a married family unit for this example, that would be a one to one relationship.

If you wanted to get a one to many relationship, you could select everything from the USER table then join on the RELATIONSHIP table to show who the partners are in the relationship:

SELECT u.username, r.user_id
FROM USER u LEFT JOIN RELATIONSHIP r ON r.family_unit_id = u.family_unit_id

Of course that would be a trivial result because you would have records saying John is in a relationship with John and Mary. But it illustrates the point. Typically, you will get more useful results for one to many relationships by adding more tables into this mix.

Upvotes: 1

andrewec
andrewec

Reputation: 136

The short answer is not really.

A one to one relationship is what you get when you establish a relationship between the primary keys of two tables.

Taking your idea: say we have a bunch of users, but we track their log-in information in a table like this:

CREATE TABLE users (
    user_id int NOT NULL PRIMARY KEY,
    username varchar,
    password varchar);

And another table like this:

CREATE TABLE user_personal(
    user_id int NOT NULL PRIMARY KEY,
    age int,
    firstname varchar,
    lastname varchar)

Once you've made the two tables, it's easy to see you have a column in each table with the same data-type (user_id) that is used as the Primary Key for that table.

If you establish a relationship between the two tables using user_id as the key, you've established a one-to-one relationship, because the user_id can only appear once in either table.

This may seem a little weird, because you could ask why you would want to separate the data out like that. Why draw the distinction between what data fields you include in one table and not the other? One easy solution is data segregation. Say we modified our second table to show this:

CREATE TABLE user_personal(
    user_id int NOT NULL PRIMARY KEY,
    age int,
    firstname varchar(255),
    lastname varchar(255),
    home_address varchar(255),
    social_security_number int)

We might want to give access to some data to some people, but not others. If you had your user-relationship table, it might be good to show some people what family/friendship relationships people have to each other, but you wouldn't want to be giving away their social security numbers and home addresses. Another big reason you might separate out the data is if your tables have a lot of data in them (think in the hundreds of columns), and only some data gets updated at any particular time. If you have millions of records that get updated, but only certain columns get updated together while the others remain pretty static, you could separate out that table into two tables with a one-to-one relationship to keep the database performance from bogging down during update processes.

Upvotes: 5

Related Questions