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

Question

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.

Why is the old table even relevant? What does "I can only query through dog_id and not owner_id" mean? Is there some problem with the obvious solution of a table with dog_id & owner_id, with (dog_id, owner_id) unique & FKs to Dogs & Owners? — philipxy, Mar 15 '17 at 03:12
*"I can only query through dog_id and not owner_id"* Normally I get the dog_id as the input from the user, so I fetch the dog attributes and owner id from the table. There is a primary key on dog_id and a unique key on owner_id. I really can't change that. As Bill Karwin pointed out, this is a classic many to many relationship and is solved using a junction table, I will probably be using that. — druk, Mar 15 '17 at 05:33
If you cannot get rid of the Uniqueness constraint on `owner_id`, you cannot use this table. Period. Fullstop. — Rick James, Mar 16 '17 at 00:35

score 1 · Accepted Answer · edited May 23 '17 at 10:30

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?

score 0 · Answer 2 · answered Mar 14 '17 at 19:04

0

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

answered Mar 14 '17 at 19:04

T K Sourabh

351
2
8

I can only query through dog_id and not owner_id, I have added this to the question now. – druk Mar 14 '17 at 19:14

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

2 Answers2