Designing database: Linking lots of values between tables

Not worded my question very well, but with these tables:

    USER TABLE                       ANIMALS
    u_id   username                   a_id   animal
    --------------------------       ---------------------------
      2    alice                       1    cat
      4    brian                       2    small dog
      7    carla                       3    big dog
                                       4    rabbit
                                       5    guinea pig
                                       etc.  

I want a user to be able to add however many animal开发者_JAVA技巧s they own to their profile.

What new tables/fields and datatypes would be the best way for me to go about this?

Thank you.

---

If you need to allow multiple types of the same animal per user (Janet can have more than one Rabbit) then do the following. Make UserId and AnimalID your primary key.

I would just do

 UserAnimals
 ------------
 UserId
 AnimalID

Filled with data your table might look like this:

 UserAnimals
 ------------
 UserId || AnimalId
      4 || 3
      4 || 2
      7 || 4

Brian has a small dog and a big dog. Carla has a rabbit.

---

Essentially you need a table which will map user ids to animal ids. If you want to add them 1 at a time, you could just use a table like so:

UserAnimals
-----------
UserID (fk to User Table)
AnimalId (fk to Animal Table)

Assuming they might own, say 3 dogs, and you want to track the number, you could either have a row per animal or you could modify the table to include a count of the animals of each type:

UserAnimals
-----------
UserID
AnimalID
Count

I'd probably do it that way if I knew that there was a good chance that folks would have multiples of a given animal, otherwise there's a little more work to do whenever retrieval takes place to arrive at a total.

I guess one could make the argument that the ID field isn't absolutely necessary for the animals either. It could just be a lookup table of strings, though that requires a bit more space for storage and complicates things a little bit if you decide that you want to modify animal names for some reason.

---

I would recommend you many to many relationship table.

Example:

table: users_x_animals
-----------------------
pid  |  u_id  |  a_id
1       2        3
2       4        5
3       2        5
4       7        1
5       4        2

This way if you have index (separate) on u_id and a_id you can either query for "animal with id X is owned by users" or the other way around "user with id x owns these animals".

Hope that helps. :)