Photo table used in many table occurences?

[El_Pablo]

Hi,

Is it a good practice to use a single table for all the photos in a database?

I explain

Let say I have a "cars" table. The user could add as many photos of the car he wants to.

Logically, we need a "photos" table linked to car table with a one-to-many relationship.

Now, I want differentiate the front pic from the others in my layout.

How would you conceptualize this structure?

[comment]

It depends on how do you know which one is the front - but in any case it would be just another relationship between Cars and Photos.

[El_Pablo]

Thanks for the fast reply!

My solution would go as follow

I will create a new TO of the photos table and name it "photoFront". Then I would create a

one-to-one relationship with the "cars" table by adding a "cFrontPhoto" in the "cars" which would be an unstored calculation that will always be "front" and adding a "type" field in the "photos" which would be used for the link.

Is that okay?

[comment]

But how do you actually establish which one is the front? Someone needs to make a decision - on what basis?

[El_Pablo]

On layout, there would be a "Front picture" field and a portal of the photos.

I attached a quick example.

The only problem I have is how to hide the front pic from the portal?

forumCars.zip

[comment]

I am afraid you are not answering my question. You wrote "the front picture" on one of the records. How did you decide it was the correct one? And, even more importantly - WHEN did you do this? Was it before adding the pictures, or did you enter them first, then looked at them and selected one?

[El_Pablo]

Actually the relationship is done automatically, when adding a picture.

If you check the schema you'll see how the relationships work.

The user only need to add the picture in the "front picture" field.

And add manually the picture in the portal. He then write down a description of the picture.

I wrote "The front picture" in the description part. I could have well wrote "This is a great blue whale" and there would be no difference in the relationships.

I'm doing this, because I want to separate the containers in a different file.

Also, I want to use a unique "photos" table in the whole solution by adding all the "fk" necessary in the "photos" table.

[comment]

Why is this so difficult? So you wrote "front" in the type field, same thing. What if someone marked more than one picture as "front"? What if you wanted to change the front picture to a newer, better one?

[El_Pablo]

Just overwrite the front picture. Since there could be only one official front picture. I just want to have a one-to-one relationship on the front pic.

The user cannot select directly the type of the picture since I won't give him the possibility.

The only way to have a front picture is inserting the picture in the "front picture" field.

Edited April 24, 2009 by Guest

[comment]

The only way to have a front picture is inserting the picture in the "front picture" field.

What is the "front picture" field? I see no such field, nor should be there such field.

I don't understand the situation at all: suppose I wanted to add a new car and some pictures of it, with one of these being the front. If you don't give me the possibility to select a picture as "front", then I don't see how this will work.

[El_Pablo]

You're totally right! There are no "front picture" field. My mistake! But there's in fact a TO of "photos" called "photoFront".

The container field which is not in the portal is "photoFront::photo".

By adding this field to the layout outside a portal it makes it a sort of one-to-one relationship. Since only the first record is shown.

If there is no "photoFront" TO, how would you differentiate the front from the rest? If there's a need in a report to show the front pic only, how would you manage this?

I'm not saying that my solution is the best, I want to learn more about other's solution.

How would you make the difference between photo types in a generic photos table if you need to show specific photo in different places in a layout?

I attached a new version that might make it easier to understand.. I hope.

forumCars.zip

[comment]

Well, if you do enable automatic record creation on the "front" relationship, then it just may work the way you have it. I still don't see your workflow, so it's hard for me to say how well it will work. Another way would be to set a field in Cars with the selected PhotoID.

How would you make the difference between photo types in a generic photos table if you need to show specific photo in different places in a layout?

If there are more than a few types, I'd make a join table of Slots, and use a portal to it as a grid.

[El_Pablo]

The main goal is to use a single photo table in the whole solution. I just want conceptualize this point for future solution.

For now it's a simple example, but later it might be a full solution where photos could be linked to multiple tables of different entities.

Let's take the example a bit further, lets add a "owners" table. The "cars" schema would be the same as the example and the "owners" would have a one-to-many relationship with the "cars" since a car has a single owner. I could then add a TO of "photos" named "photoOwner" which would then be linked to the "owners" table.

If there are more than a few types, I'd make a join table of Slots, and use a portal to it as a grid.

I need more explanation on the "Slots" thing.

[El_Pablo]

I attached the example with the owner.

Like said I'm interested in you slots table theory.

forumCars.zip

[Fenton]

Here is how I would likely do it. Comment's suggestion of setting the "front" photo's ID into a field in Cars and using that as the "front" car relationship is by far the simplest. But there is one side effect, which may be good or bad, depending on what this "change" means to you.

The side effect is that it changes the modification date of the Car record. In some cases this is to be avoided, as you may not consider this a real modification, and it might matter. In that case here is another more complex method, which sets the flag field in the Photos table instead.

forumCars_fej.fp7.zip

[El_Pablo]

Thanks Fenton, it's a nice method. If you check the last file I sent, I updated with a new degree of complexity by adding a owner with his photo. Using the same photo table.

Is it possible to use the same method as yours? I added fkOwnerID in the photo table. Is that a good method, since I might need the photo table in other TOs.

Edited April 24, 2009 by Guest

[comment]

I am not sure putting owner photos and car photos in the same table is necessary a good idea. But I get the feeling that this is largely a theoretical discussion, and this makes it impossible to discuss the pros and cons of any method.

For an example of using slots, see:

http://fmforums.com/forum/showtopic.php?tid/176396/

[El_Pablo]

Actually, the photo tables have usually the same structure with the only difference of the foreign keys and types. It's more for maintenance purpose than simplicity.

Instead of creating a foreign key for each relationships, it might be easier to use a single foreign key as text in the photos. In each parent tables, there could be a calculate secondary primary key which combine the serial nuumber with the table name.

E.g.

Cars table

pkCarID <-- serialNumber

_cCarID = pkCarID & "_car"

Owners table

pkOwnerID <-- serialNumber

_cOwnerID = pkOwnerID & "_owner"

The relationship between a "photos" TO and the parent would be fkParentID = _cParentID.

How is that partial solution?

[Fenton]

Yes, this file uses my method with your multiple types. There is not much difference in the script really. The basic chore is to reset any other photo for the same car which has the same "type", as there is only one that can show. You must reset it (and auto-enter on creation) a "default" type, to use for the portal's relationship, to show only those. You cannot use "≠", as it precludes [x] Allow creation of related records (you can't create something that "does not equal").

The script is run by a Script Trigger, OnModify of the Type field. So it requires FileMaker 10. You could do it with a button next to the Type field, but that would be kind of awkward.

I also added a "plain" table occurrence for Photos and its layout (related directly to Car_ID). I don't much like having multiple [x] Delete related records relationships going to the same table. So the plain one is the only one with that, to delete all Car photos. It also makes more sense if you're using Photos for other things also.

[P.S. Changed file; used the new Photos relationship to Go To Related Record, instead of Find for CarID; simpler.]

forumCars_fej.fp7.zip

Edited April 24, 2009 by Guest
changed file

[El_Pablo]

I like the OnModification thing.

The only thing that I would add is a portal with all the car's photos to make in sort that the user could undo the move.

Thanks for your suggestion!

[Fenton]

Well, you could easily show all the photos. But your interface is more "economic". Right now, the one deficiency of your interface is that once you pick something for a particular role (front, rear, etc.), the only way to change it is to replace it with another. A simple button to "clear" one of the role photos could do that. It would need to set that photo's Type field to "unassigned", which would return it to the lower portal.

forumCars_fej.fp7.zip

[El_Pablo]

Yep, your file is working nice. If you don't mind I will keep it in my samples folder.

Thanks again!