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. :)