I am struggling with an issue and hoping I can some suggestions on how best to proceed.
I have a database that tracks tags for pictures. each record is a picture, and for each record the customer wants to tag the picture as having various attributes. So currently we have a PICTURE table that has about 100 different fields that are a simple checkbox. User checks the box and it means that this criteria is in the picture. These 100 fields are set using a value list of "1", so if checked the field contains a value of 1, otherwise it is empty.
Although things are working fine, I'm really struggling with this from an efficiency point of view and wanting to find a way to make the table MUCH more narrow. and since this table is referenced many other places via portals ... I dont want to send down a ton of data that isn't needed. The main problem is that a typical picture has only 3-5 tags checked, so out of these 100 fields, only 3-5 are checked at any one time.
a portal would work beautifully obviously since it would simply create 3-5 related records per picture and the picture table would stay narrow as a result, problem is ... the customer wants a screen of checkboxes and not a portal like interface. They want to come to a screen with an alphabetical list of all possible tags ... and quickly check the tags that apply for this picture.
I was toying with creating a one to one relationship with a new table called "PICTURETAGS" and putting the 100 fields in this new table (removing them from the original PICTURE table. This would make the original table narrow, but seems inefficient again to have 100 fields in a table.
Then I thought maybe we create a one to many type of relationship but don't show a portal, but rather show the checkboxes and have each checkbox use a different relationship to the foreign table to display a checkmark if there is a related entry. This would require 100 TO's ... which seems ridiculous as well.
Lastly I was pondering using a one to many, and using a virtual list on record load to load the checkboxes for each picture. This I assume would require 100 global fields in the PICTURE table and a script to populate the globals on record load. This on the surface seems reasonable, but I don't want to introduce a delay as the user is going from one picture to the next while globals are being populated. I also think this sounds like a lot of work to deal with FINDs (when the user wants to enter find mode and check a bunch of tags to find which pictures match their criteria).
Has anyone had any experience getting portal type information to appear in a checkbox style look (again, where you see ALL the fields whether they are checked or not). Maybe I should just keep things as is, it just sits wrong with me knowing these many fields are being used.