MySQL - Can multiple Foreign Keys be linked to a main table?

[Guest]

Hello all,

Here's a pretty straightforward scenario and question.

I'm building the database to manage my online photo galleries.

Some of the galleries will feature more than one model.

Because of this, I have a table for model names in order to credt those in the photos.

SO...

If one gallery has say, three models, another has two, another has four, how do I assign three, two or four separate foreign keys to the main table (called Galleries)?

I want to use this data to display model names in the text description of each gallery and feel that this would streamline things for me.

Please let me know if this makes sense.

Thank you in advance for your help!

Sincerely,

wordman

[bregent]

>If one gallery has say, three models, another has two,

>another has four, how do I assign three, two or four

>separate foreign keys to the main table (called Galleries)?

You are talking about a many-to-many relationship. You need to create a link (relation) table with two columns. This stores the id of the gallery and the id of the model. So, if you gallery has 3 models, the link table would have 3 rows - each row would contain the same gallery id and the individual model id's.

[Guest]

bregent,

Thank you for that! Without getting into the specifics, if I understand this correctly, I should build this table anticipating the maximum number of models appearing an a shoot and size the table accordingly, correct?

Many thanks for taking the time to pick up this thread!

Sincerely,

wordman

[bregent]

>if I understand this correctly, I should

>build this table anticipating the maximum

>number of models appearing an a shoot

>and size the table accordingly, correct?

I'm not sure I understand. Are you asking about database space allocation? The table width will be small - only two columns that each store an id value. The number of rows is the number of models associated with each gallery. If gallery 1 has four models and gallery 2 has 3 models, that's 7 rows total.

[David_Powers]

Just to add to bregent's explanation, when creating the lookup table, both columns should be designated as a joint primary key (do not use auto_increment). This ensures that each combination is unique.

gallery_id	model_id
1	1
1	2
1	3
2	1
2	4
3	3
3	4

[Guest]

David and bregent,

Thank you both for always coming to my rescue!

The issue here is that I never know from shoot to shoot how many people I will be working with, so it may not be feasible to build a database with the capacity to cover all possible probabilities. David, I did review your suggestion in that lifesaver, 'PHP Solutions' and recall seeing the very thing you have so kindly suggested. I think, however, that I may be trying to do too much.

The help from each of you is sincerely appreciated!

Cheers,

wordman

[David_Powers]

Wordman-GL wrote:

The issue here is that I never know from shoot to shoot how many people I will be working with, so it may not be feasible to build a database with the capacity to cover all possible probabilities.

That's the beauty of a lookup table. It consists of just two columns, the primary keys of the records you want to look up. So, you can have as few or as many models connected with a shoot as you like. Looking at it in terms of primary keys, it looks confusing to start with, but it's a lot easier if you think of it this way:

shoot	model
wedding	Angela
wedding	John
bikinis	Angela
bikinis	Monica
bikinis	Sarah
fitness kit	John
fitness kit	Sarah
baby clothes	David

You could have twenty models associated with a shoot or a hundred if you want. Because the lookup table stores only the primary keys of each record, it doesn't take up much capacity, and databases zip through columns of numbers at lightning speed.

[Guest]

David,

OOOHHH!!! I think I was looking at this BACKWARDS. It's not that I would need to populate my main 'Galleries' table with model information, rather I would keep the model info solely in the lookup table. Once again, your ability to explain these things has worked perfectly!

So to issue model credits, I just pull a query for the lookup table and then display that data. Excellent. Thank you so very, very much!

Sincerely,

wordman

[bregent]

>So to issue model credits, I just pull a query

>for the lookup table and then display that data.

Yes, you've got the idea now, but I just want to add some clarificaiton. David called this a lookup table. It's similar to a lookup table but it's really not the same. A lookup table is commonly used in picklists to allow users to select from different options. Or, they can be use to lookup details of other id values. For example, you could store a State id in the main table, and then pull the full state name from a lookup table.

When talking about many-to-many relationships, the table that links the two is more commonly referred to as a link, association, or intersection table.

[Guest]

I removed the last post because I have altered the database design. Will share it soon.

Thanks!

wordman