CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
druk
druk

Reputation: 603

How do I share resources in a relational-database (specific case)?

Say I have a table Dogs

mysql> select * from dogs;
+----------+-------------+----------+
| dog_id   | tail_length | owner_id |
+----------+-------------+----------+
| dog-id-1 | 1 cm        | owner-1  |
| dog-id-2 | 2 cm        | owner-2  |
| dog-id-3 | 3 cm        | owner-3  |
+----------+-------------+----------+

Where dog_id is a primary key and owner_id is unique. Now I want sharing of dogs to be possible. so the table can be.

    +----------+-------------+----------+
    | dog_id   | tail_length | owner_id |
    +----------+-------------+----------+
    | dog-id-1 | 1 cm        | owner-1  |
    | dog-id-2 | 2 cm        | owner-2  |
    | dog-id-3 | 3 cm        | owner-3  |
    | dog-id-3 | 3 cm        | owner-1  |
    | dog-id-3 | 3 cm        | owner-1  |
    +----------+-------------+----------+

But it is not possible as dog_id is a primary key and owner_id is unique in the table. A dog can possible be shared with 10000+ users.

Due to constraints of backward compatibility I cannot remove the primary and unique key constraints of the original table and I have to use mysql to do this. What would be the best strategy to achieve sharing?

Additional Constraint: I can only query through dog_id and not owner_id.

Upvotes: 1

Views: 117

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562671

What you have is a many-to-many relationship.

  • A dog can have many owners
  • An individual owner can own many dogs

This is a common problem in relational database design. What you need for a many-to-many relationship is to define another table.

+----------+----------+
| dog_id   | owner_id |
+----------+----------+
| dog-id-1 | owner-1  |
| dog-id-2 | owner-2  |
| dog-id-3 | owner-3  |
| dog-id-3 | owner-1  |
| dog-id-3 | owner-1  |
+----------+----------+

I can only query through dog_id and not owner_id

I don't understand why this is relevant. The data organization is about Third Normal Form, not about how you will query the data.

The only alternative you have is to store multiple owner ids in one column of one row, which is not a valid design for a relational database. See my answer to Is storing a delimited list in a database column really that bad?

Upvotes: 1

T K Sourabh
T K Sourabh

Reputation: 363

Create another table Owners and supply a Foreign Key to the table Dogs.

Upvotes: 0

Related Questions