Which relationship is faster?

[El_Pablo]

Hi,

I'm developping a database concerning sold houses. The system needs to have the following photos of the house front, rear, neighbourhood and aerial.

I want to separate the photos from the main data file for maintenance purpose. Also I want to use the photo database file for other tables.

I thought about the following relationship :

sales (pkSaleID, pkSecondaryID, frontPic_id, rearPic_id, neighbourhoodPic_id, arealPic_id, ...)

photos (pkPhotoID, fkParentID, type, photo_container, hasPhoto, ...)

Where :

sales::pkSecondaryID := pkSaleID & "_sales";

sales::xyzPic_id is a fix number to define the type of photo in this case 1, 2, 3, 4.

photos::fkParentID is related to sales::pkSecondaryID.

But it seems to me that such a relationship is slow.

Is there a better way?

[David Jondreau]

Why store the Photo IDs in the Sales table at all? fkParentID and Type are all you need.

If you're going to use the Photos table for photos of other tables types besides Sales, you're going to need to ensure that the ParentID is unique in your file.

In these situations, I make all PKs autoenter serial fields of text type with a 4 letter prefix (SALE) followed by a few zeros (SALE0000123, SALE0000124, etc).

[El_Pablo]

In the sales table, it's not really a photoID, but a typeID to differentiate the photos. Maybe, I should name the fields "photoType_front", "_rear", ...

[David Jondreau]

I'm not totally clear on the purpose of those fields, but my instinct is they are unnecessary.

[El_Pablo]

How would you then show in the front, rear, neighbourhood and aerial photo on layout without a portal on a given order?

[David Jondreau]

With a portal.

A filtered (new to FM11!) single portal for each type would do it. Or just a regular portal showing the type along with the photo. You can custom sort by type as a value list if that's important.

[El_Pablo]

The user wants to insert the Front picture in the front space, rear in the rear and so on. And in no given order.

How about one TO per photo type?

[David Jondreau]

I try to keep my tables, fields, and relationship graph clean of interface elements when I can. Doesn't always work. But here, I'd do something like the attached.

mysandbox.fp7.zip

Edited August 17, 2010 by Guest

[comment]

This is cool (though it took me a while to realize there was a script trigger). Wouldn't it be neat if the portal filter itself could force the value?

[David Jondreau]

You could define a $$var in the portal filter and have the type field auto-enter it, but you run into commit issues (moving from portal to portal without committing screws it up).

If you think of something, please share it.

[comment]

You could define a $$var in the portal filter and have the type field auto-enter it

Wouldn't the last portal to evaluate (in layout's stack order) determine the value - no matter which portal you are entering into?

[LaRetta]

Wouldn't the last portal to evaluate (in layout's stack order) determine the value - no matter which portal you are entering into?

Yes. In fact you can use conditional format (with layout-level variables) to evaluate in sequence (in a recursive effect) based upon the stacking order and, using that technique, you can calculate based upon prior values.

Edited August 17, 2010 by Guest
Changed some wording

[comment]

you can use conditional format (with layout-level variables) to evaluate in sequence (in a recursive effect) based upon the stacking order and, using that technique, you can calculate based upon prior values.

So how would you use this?

[LaRetta]

Wouldn't the last portal to evaluate (in layout's stack order) determine the value - no matter which portal you are entering into?

Hi Michael,

I was only agreeing with you. Stacking order is the key as you said ... and the power of $variables on a layout with conditional format. I got excited because that's exactly what I'm working on and I confess that I didn't really read the thread (usually I do). I don't have time right now to create a demo other than very simple basic - most of my work with them is being created in our solution. Since $variables remain static on a layout, they can be used in conditional formats to move variable values forward through $variable since conditional formatting evaluates in stack order.

Anyway, here's a simple example with three filtered portals and how values can be moved ahead through each filtered portal (but the prior accumulated value using only layout variables). I'm just learning the techniques and the power of conditional format and layout variables thanks to you and Mr_Vodka. John showed me this concept and the only hang is that conditional format (in this method) only works on visible portal rows.

UPDATE: But the concept can be used through list records as well, usually requiring simple script trigger. BTW, notice I use 'I declare variables' which you came up with. We put that on the top of almost every layout and keep all of our variables in it. We use layout-level variables for most all display calcs now instead of creating calculations. We also attach 'dummy' as object for special use.

AccumFilteredPortalAmts.zip

Edited August 17, 2010 by Guest
Added updates

[comment]

Ok, I see. But you are passing the value from record to record - not from a portal into a field, as David suggested. So that *is* useful, just not here.

For the original issue I think I'll buy the script-trigger idea (subject to speed tests).

[David Jondreau]

Wouldn't the last portal to evaluate (in layout's stack order) determine the value - no matter which portal you are entering into?

It sure does...That won't work then.

[El_Pablo]

Since each picture type is unique to the record, wouldn't that be easier to manage using a type field in the parent record?

Check the attached file to see my method?

mysandbox.fp7.zip

[David Jondreau]

Depends on what you mean by easier.

The Type field is an attribute of a photo, there's no reason to store the type in the parent record. If I were going to do it your way, I'd make those type auto-enters global calcs instead. Saves some on size and prevents users from accidentally changing the keys. At the least make them standard calcs.

But I try to follow the principal that data and interface be kept separate.

What if you want to add a new type of photo, or delete an old one? What if you decide you don't need to separate out the types of photos? You've got to change your table definitions and your relationship graph. I'd rather make a change to a layout than a change the the data structure.

[El_Pablo]

Actually, the old system kept the photos in containers with the main data, which is quite problematic since the file is getting huge (5 Gb+). So I decided to separate data and photos.

I need to balance management and speed.

The problem is that I might have 20 different layouts with sales and photos. And also, I have a report that links sales with a joint table.

reports--sales-

Using my method seems to be quite slow when there are hundred thousand sales.

Using your method is faster, but I need to manage all the layouts independently and use strings to filter out data.

There is also 360 Works' SuperContainer that I'm looking at.

I'm still in a dilemna...