Join table or flag field

[bluearrow]

Hi,

I have the following problem. A database of CLIENTS may have clients in the following situations:

Prospectives

Active

Canceled

Void

What do you think it is the best way to handle this?

1.- Create a single CLIENT table and have several flag fields to identify each situation. For instance:

flag_Prospective

flag_Active

flag_Cancelled

flag_Void

2.- Create the following tables:

CLIENT_PROSPECTIVES

JOIN_CLIENTS

CLIENT_ACTIVE

The user will always start by creating a Prospective client. Whenever the client makes an order (or whatever else activating mechanism) then the user must move the client from prospective to active and make sure some compulsory fields for active clients are filled in. The join table will either contain a foreign key field for Prospective or Active client, depending on the situation (but not for both, of course). The client table will contain flag_Cancelled and flag_Void fields.

3.- Create the following tables:

CLIENT_PROSPECTIVES

JOIN_CLIENTS

CLIENT_ACTIVE

CLIENT_CANCELLED

CLIENT_VOID

Similar to the previous option, but in this case the CLIENT table will not include flag fields as defined. Instead, the join table will act as the filtering mechanism, and will contain foreign keys for each of the 4 tables (only one at the time can be present, of course).

My doubts come from the fact that the client will have Tenders, Invoices, etc, and I am not sure which of the 3 options described could be the best one after all for handling the whole picture (maybe having separate tables and a join file is completely wrong and could make the database unmanageable...)

I would appreciate your input and experience on this.

Regards

[Søren Dyhr]

Well I would in this case break 1NF and use a checkbox field instead of a join table as such.

But it's different from all your suggestions, so you better explain the purpose with the various structuring strategies and what needs to get accomplished by the structure you had in mind ...here's a template how I would deal with it:

--sd

clients.zip

[bluearrow]

Dear Søren,

Thanks for your reply and the file. It is not exactly what I had in mind.

Here is the goal I am trying to achieve:

- Allow users to freely create new prospective clients without checking any compulsory field.

- Make some fields compulsory if a prospective client is clasified as Active (the user must fill in those fields).

- Completely separate each of this clients for searches, accounting info, etc. This is the most important point and the one that is leading me to all this strategies and reasoning.

- Prospective, Cancelled or Void clients should not appear in Client searches. This can be achieved in various ways, but one ways it is the one described as Strategy 3. Other options could be to allow the user to select from a checkbox or client Situations, but this means the selection is not automatic. I could run a previous search I am not sure this is correct also in FM8.5 or there is a better alternative.

- A Cancelled or Void client could already have generated some Tenders and Invoices. I do not want them to appear in my reports when I select a client/invoicing report. This can be achieved by filtering the client (a previous search, flagging the clients, etc.), but it might be better to completely separate clients in different situations. This way, reporting is much easier and does not requiere a previous search and a current found set. I am not sure. I do not have enough experience in FM8.5 to tell. In FM6 I would opt for filtering the search and running the report with the current found set, but ¿is this the best option in FM8.5?

Basically, I am not sure if the way I did things in FM6 is the way I should do things in FM8 (where, for instance, there is no practical limitation on the number of tables, the way you show information is linked to a TO, etc.)

Regards

[bluearrow]

...one last comment that it is also somehow related to this issue. In an invoicing system, when you move from one year to the other you have the option to move the invoices to a OLD_INVOICES file (table in FM8.5) or simply keep them in the same file and filter searches, reports, etc. In FM6 you were limited by the number of files, and I have to keep them in a single file. However, FM8.5 opens up new posibilities. Is it better to move them to a separate file? (it is a very similar situation to the one I describe in the previous messages). Same could be said and applied to line items.

How do you do it?

[Mikhail Edoshin]

I'd use separate tables.

(See also: http://www.fmcollective.com/2006/12/postdesign_cauc.html)

[Søren Dyhr]

I'm having problems with pinpointing what part of the migration papers you miss, but let me instead show you in a template how i would deal with:

I do not want them to appear in my reports when I select a client/invoicing report.

While knowing there is a myriad of ways to accomplish such behaviour; could it be that what you miss to recognize the difference between TO and tables?

Please read Harris' whitepaper:

http://www.digfm.org/ref/FM7_key_concepts.pdf

--sd

bizz.zip

[bluearrow]

Thank you Mikhail,

The article provided in the link is exactly the type of reasoning I was looking for. It was difficult to describe my point (sorry if I mislead you Søren).

Now Mikhail I have a couple of questions/requests:

1.- Suposse I follow the idea expressed in the article and create new tables just with the clients key field. The CLIENT_ACTIVE would only contain keys for active clients, same for the CLIENT_VOID, etc. There would be a CLIENT_ALL table that includes all of the keys + all of the fields for clients. I guess the CLIENT_ACTIVE table should also contain the flag_active field. Is this so? Should it also be present in the CLIENT_ALL table?

2.- When it comes to searches, a search on active clients should be carried out in the CLIENT_ACTIVE table, including related fields coming from the CLIENT_ALL table. Does this pose any speed issues? At least in FM6, if you search in a field that is not present in the current file, searches are noticeable slower than in a field inside the parent file. Would this be the same in the new table/to structure present in FM8.5? Take into account that I am talking of a search among several thousand records. All search fields would be indexed, of course.

3.- Do you know of any link to a sample database that uses this approach? It would be very useful to have it.

Regards

[bluearrow]

Dear Søren,

Thank you very much for your reply and for the file and your reply, and for pointing out the TO/table issue. No, I was not confusing TOs and Tables. I really meant to have separate tables, not just different TOs. The link provided by Mikhail deals with this problem and more or less comes to say that yes, it is better to split them in different files (at least, to my undertanding of the short article). This is a different way altogether to build the database, as opossed to creating different TOs for each view (a TO for active clients, a TO for void clients, etc.).

After reading the article I was left whit several questions that I have exposed in a reply message to Mikhail.

Now that I am moving to 8.5 from 6, I am starting to realise the huge different of approach from one version to the other. I guess I will be sending a lot of questions to the forum until I feel confortable building the dsatabase.

Thanks again for your time.

Regards

[Mikhail Edoshin]

1.- Suposse I follow the idea expressed in the article and create new tables just with the clients key field. The CLIENT_ACTIVE would only contain keys for active clients, same for the CLIENT_VOID, etc. There would be a CLIENT_ALL table that includes all of the keys + all of the fields for clients. I guess the CLIENT_ACTIVE table should also contain the flag_active field. Is this so? Should it also be present in the CLIENT_ALL table?

As far as I understand it would be better to use more fields in the tables, not just a key field. I.e. if you have fields that are used for active clients only, you should place these fields in the Active Client table. The mother Client table will contain only the fields you need to enter a prospective client. The flag is no longer necessary: in the new scheme an active client is the client that has a related record in the Active Client table.

2.- When it comes to searches, a search on active clients should be carried out in the CLIENT_ACTIVE table, including related fields coming from the CLIENT_ALL table. Does this pose any speed issues? At least in FM6, if you search in a field that is not present in the current file, searches are noticeable slower than in a field inside the parent file. Would this be the same in the new table/to structure present in FM8.5? Take into account that I am talking of a search among several thousand records. All search fields would be indexed, of course.

The only answer would be to test it, but I don't think it would be slow. Searching have been greatly improved, especially for apps hosted on the server.

3.- Do you know of any link to a sample database that uses this approach? It would be very useful to have it.

Nope. I cannot remember any sample I've seen :D

[Søren Dyhr]

It was difficult to describe my point (sorry if I mislead you Søren)

Don't worry, I use this forum to challenge my thinking - and this thread made a wondeful oppertunity, although I here flew under the radar

Mikhail, thanks again for being such a great inpiration!

--sd

[Søren Dyhr]

it is better to split them in different files (at least, to my undertanding of the short article). This is a different way altogether to build the database, as opossed to creating different TOs for each view (a TO for active clients, a TO for void clients, etc.)

You mean tables not files! I'm about to get ajourned with the reasoning behind, but no I see no traces of different files just the separation model swiftly being brought up???

But then is there a question, the same as yours more or less... what fields is then required in the Active students table beyond the recordID and the foreign key, since a lot of stuff is easily enherited ...alright I can see a field like academic year ...or am i right off here???

--sd

[bluearrow]

You mean tables not files!

Upsss... yes, I meant tables

But then is there a question, ... what fields is then required in the Active students table beyond the recordID and the foreign key, since a lot of stuff is easily enherited ...

--sd

I am exactly in the same situation as yours. I was thinking of creating several Client tables, each just with the foreign key. Then, if a Client needs to be moved from Prospective to Active, you would just copy the foreign key. Same goes for any other situation. I do not clearly see the need to copy any other fields except for:

- Fields that should only be present in one table, but not in the other. For instance, a prospective client might not need a VAT number, whereas an Active client would surely do. Then, this field would be present in the CLIENT_ACTIVE table but not in the CLIENT_PROSPECTIVE table. This sounds reasonable.

- Searching (I tend to think it would be much easier and faster).

- Calculated fields formulas (I guess it would be easier to formulate them if most of the fields are in the same table).

However, several questions remain:

- The mechanism for copying the foreign key and one or two other fields is pretty straithforward, but what happens if the table has hundred of fields? wouldn't this add a point of error to the database with all the scripts and the copying going on in the background while moving a record from one table to the other?

- What happens with new fields and field definitions changes? For instance, if you need to create/recreate every field that is present in one table to another table this sounds like a nightmare. Lookups could be used for the "second" table (for instance, CLIENT_ACTIVE), but I do not like the idea very much. And new fields might need to be created in every table anyway.

- If you do not copy fields from one database to the other (for instance, Client_name should be present in CLIENT_PROSPECTIVE but also in CLIENT_ACTIVE and subsequently in CLIENT_VOID, etc.), how do you easily search? Searching in fields from another table might not be a good idea if you have many records and the searches are very frecuent. After all, easy and fast searching was (is) one of the driving reasons behind my initial reasoning.

- what implications would this have on the rest of the database design? I mean, would it add a huge amount of complexity to issues like TOs, etc.

Just thinking aloud...

[Søren Dyhr]

but what happens if the table has hundred of fields

Is it likely to happen? take a look at this video:

http://previews.filemakermagazine.com/videos/513/DataTagging_full.mov

Applying this principle as well will solve that.

For instance, if you need to create/recreate every field that is present in one table to another table this sounds like a nightmare.

Oh no, you're tunneling in pre fm7, if you take a look at the template I made you yesterday wasn't all fields in the inv-items a lookup, but just the fields ushered in from the respective related table ...eventhough it might originate several relational jumps away.

http://www.newcenturydata.com/downloads/gtrr_multi_hop.zip

Searching in fields from another table might not be a good idea if you have many records and the searches are very frecuent

Not the issue any more with fm8+ while there was some issues with fm7 but nowhere near the bother we had with fm6 and before, where you always needed to jump to the related and then gather the foreingkeys with a special layout and the Copy All Records scriptstep.

--sd