What is the most efficient way to...

[Transmasco]

Hello everyone, it's been a while since I've been here and worked a solution in FM (last was FM7), so my limited skills are very rusty. Now that FM12 is here, I'm going to re-develop out entire system. So bear with me...

 

I have an Orders table which looks up data from the Clients table based on client_ID. Each client has a different way of getting billed (some by rate, some by tariff, some by a per km charge, some by destination, etc.). These rates, charges, etc. also change very frequently, so I'd like to keep a record of those changes.

 

What would be the most efficient way to a) determine the rates per customer (a bunch of fields or separate table in a portal?) and relate the tables so that the Orders table can access the correct and up-to-date information for the specific client.

 

As an side...The total solution is around 25-30 tables. Is it better to cram all tables on a single file or break them up into a few files (one per department)?

 

Thanks so much for the help!

[Wim Decorte]

I would go with a separate table.  Much easier to keep track of history.

 

As to breaking up the solution: given the FMS12 backup changes the decision is best made with an eye on data that changes often and what is fairly static.  Splitting out static data will reduce backup times and reduce disk space requirements allowing for more frequent backups.

[dansmith65]

As to breaking up the solution: given the FMS12 backup changes the decision is best made with an eye on data that changes often and what is fairly static.  Splitting out static data will reduce backup times and reduce disk space requirements allowing for more frequent backups.

Wim, can you explain your thought process on this? How is having static data in a separate file (hosted by the same server, I'm assuming) going to reduce backup times and disk space?

[Transmasco]

Man am I rusty!

 

I have a table with fields for country, state, county, city. I'm planning to create value lists from each field using self-joins. So that when I choose the country from the country drop-down, only states from that country show in the states drop-down, and so on with county and city. Is it possible to create self-joins to create the value lists and them use those value lists on other tables?

 

Thanks in advance