Naming conventions: fields from value list tables

[Ignasi Alemany]

My solution has about 25 tables used to store value lists. Each of these tables is made of a primary key field and a value field. The primary key (simply named id) matches a secondary key (named id_valueList) in another table.

This setup creates an extremely long list of primary and secondary keys for fields that just need to be validated by VLs, so it adds some complexity and confusion to the design.

In the naming standards section I couldn't find any mention to table based VLs, I would be grateful to know your opinion on this.

Thanks,

 

[Daniel Smith]

I only use two tables for most value list's: one to store the value list name, and the second to store all values for all value lists. A little like this: https://www.youtube.com/watch?v=DjipjdCQrjk

> This setup creates an extremely long list of primary and secondary keys for fields that just need to be validated by VLs, so it adds some complexity and confusion to the design.

I don't understand this part; you can validate a field by a valuelist without a relationship. My best guess is you're doing it some way that I wouldn't, which is why I don't understand.

--
You received this message because you are subscribed to the Google Groups "FileMaker Development Standards" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fmstandards+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ignasi Alemany]

Thank you Daniel, that tip is very useful and will help me reduce design complexity.

My VLs are designed to make literal values independent from the key values on each list. This is because I want the final table to store only a key which leaves the possibility to modify the literal values on the VL table and get its new value into existing records.

To display the literal value requires each field instance on the layout to have a relationship to the VL table where the literal is taken from. This is the added complexity I'm dealing with.

I'm also interested to discuss field naming: since I only store key values, does it make sense to name every primary key on a VL table 'id' and every foreign key 'id_table' even thought is only for VL purposes?

[Daniel Smith]

> My VLs are designed to make literal values independent from the key values on each list. 

FYI: even though the video I linked to didn't show it, you can still do that with the two-table method I mentioned.

> To display the literal value requires each field instance on the layout to have a relationship to the VL table where the literal is taken from. This is the added complexity I'm dealing with.

You could do away with that extra table occurrence if you defined the field as a Pop-up menu, showing values from a value list that returns an id, but shows a text value (and enable the "Override data formatting with value list" if you're in FM15).

> I'm also interested to discuss field naming: since I only store key values, does it make sense to name every primary key on a VL table 'id' and every foreign key 'id_table' even thought is only for VL purposes?

I think this depends on the database. I'm working on fmQBO2 right now and choose the `id_table` naming standard for Terms on an Invoice, Tax Codes, and the Product/Service. You said you have 25 value lists, though, so the same standard might not make as much sense for you.

The part I like about `id_table` is that it's clear that the actual value is in another table, whereas if you named the fields something like `make` and `model` (thinking of the make and model of a car), I would expect the value of the field to be text like "Ford".

If you combine all your 25 value list tables into the two-table setup I mentioned, then all the related values come from the same table (which I call ValueListItem) and I would name the foreign key fields: `id_ValueListItem_make` and `id_ValueListItem_model`. Perhaps that common prefix is the naming standard you're looking for? Since it now makes it clear that the value is in a related value list table, and separates the foreign key field from more "standard" related tables like `id_Customer`.

If that isn't what you're looking for, I think specific examples of field's and value lists you're using (and how many per table) would help.

--

[Ignasi Alemany]

Thanks for one more useful tip: I didn't notice FM15 allowed pop-up menus to show the literal and store the key, I'll definitely use this and together with the related tables method will reduce a lot of complexity.

Regarding naming: I agree key with you that field names must make clear that its values are not literals but keys, however I'm not sure about keeping the 'id' prefix, perhaps this should be reserved for the more relevant relationships and use a specific prefix like 'valueListItem' (as you suggest) or 'vl' (e.g. 'vl_model').

Every opinion about naming will be welcome.

To unsubscribe from this group and stop receiving emails from it, send an email to fmstandards...@googlegroups.com.

[Malcolm Fitzgerald]

On 11/11/2016 2:29 AM, Ignasi Alemany wrote:
> Thanks for one more useful tip: I didn't notice FM15 allowed pop-up
> menus to show the literal and store the key, I'll definitely use this
> and together with the related tables method will reduce a lot of
> complexity.
>
> Regarding naming: I agree key with you that field names must make
> clear that its values are not literals but keys, however I'm not sure
> about keeping the 'id' prefix, perhaps this should be reserved for the
> more relevant relationships and use a specific prefix like
> 'valueListItem' (as you suggest) or 'vl' (e.g. 'vl_model').
>
> Every opinion about naming will be welcome.

Is the reason for attaching a value list to the field is to place an ID
into it? If so, the value list is secondary, the id is primary.

m.

[Ignasi Alemany]

ok, but I wonder if it would be better to use a specific prefix (like "vl") to the fields that contain value lists keys in the main table or keep using the standard 'id' prefix

[Corn Walker]

Just because it's in a relationship doesn't mean it's an identifier.

On Nov 11, 2016, at 1:48 PM, Ignasi Alemany <nai...@gmail.com> wrote:

> ok, but I wonder if it would be better to use a specific prefix (like "vl") to the fields that contain value lists keys in the main table or keep using the standard 'id' prefix

Cornelius Walker
Hatfield, MA

[Jeremy Bante]

To elaborate, your data records aren't children of the value list records. The value list records aren't "entities" in the database normalization sense of the word. The link to a value list record is just an attribute of your data record. If you're fishing for a convention to follow, consider "Code" as a suffix. For example, if your data record is a person, and the attribute is an occupation (with a list of occupations in a separate table), you could name the field "occupationCode".

On Friday, November 11, 2016 at 12:27:48 PM UTC-8, corn wrote:

Just because it's in a relationship doesn't mean it's an identifier.

[Agi]

The only reason I'm not crazy about this method is because often you will find yourself needing the actual literal name to be stored, e.g. if you want to show it somewhere else but not as a pop-up or search on it.

[Ignasi Alemany]

your data records aren't children of the value list records. The value list records aren't "entities" in the database normalization sense of the word. The link to a value list record is just an attribute of your data record. If you're fishing for a convention to follow, consider "Code" as a suffix

I agree completely and the "Code" suffix sounds like a good idea. In my humble opinion this idea should have a dedicated section in the "Naming" section of www.filemakerstandards.org.

Thank you. 

[Ignasi Alemany]

I guess you refer to the VL literal field name in the "single table for multiple VLs" technique.

I think that name will always have its Table Occurence name attached, so there shouldn't be any confusion as pop-ups can also be used for search or printing. For example "OccupationsValueList::ID"