How Many Tables Do I Need?

[HonkyCat]

We are a lab that tests compressed air for our customers. With FM 7 we want to be able to better track when each customers compressor system is due to be tested. One customer can have many systems to test and it is possible for each of their systems to be on a different testing schedule.

I know we need at least 3 tables. A table for customers, a table for contacts, and a table for the compressor systems. One customer can have many contacts; and one customer can have many systems to track.

Some of our customers also have customers of their own (they are distributors for us). In the past we did not keep track of their clients compressor systems (they handled it on their own). But with FM 7, we want to start keeping track of when their customers are due for tests as well. However, I don't want to lump them in with our DIRECT customers because we need someway to know that they 'belong' to another customer. This is where I start getting confused on how to design the solution.

Would it be best to have another table (we'll call it "Their Clients") and then a completely separate table with 'Their Client Contacts' and then another separate table with 'Their Client Compressor Systems'? Or is there a way to keep track of all of this information in the original 3 tables I described? The 'key fields' involved are tripping me up. I hope this makes sense. Thanks for any help you can give me!

Kristine

[HonkyCat]

We are a lab that tests compressed air for our customers. With FM 7 we want to be able to better track when each customers compressor system is due to be tested. One customer can have many systems to test and it is possible for each of their systems to be on a different testing schedule.

I know we need at least 3 tables. A table for customers, a table for contacts, and a table for the compressor systems. One customer can have many contacts; and one customer can have many systems to track.

Some of our customers also have customers of their own (they are distributors for us). In the past we did not keep track of their clients compressor systems (they handled it on their own). But with FM 7, we want to start keeping track of when their customers are due for tests as well. However, I don't want to lump them in with our DIRECT customers because we need someway to know that they 'belong' to another customer. This is where I start getting confused on how to design the solution.

Would it be best to have another table (we'll call it "Their Clients") and then a completely separate table with 'Their Client Contacts' and then another separate table with 'Their Client Compressor Systems'? Or is there a way to keep track of all of this information in the original 3 tables I described? The 'key fields' involved are tripping me up. I hope this makes sense. Thanks for any help you can give me!

Kristine

[HonkyCat]

We are a lab that tests compressed air for our customers. With FM 7 we want to be able to better track when each customers compressor system is due to be tested. One customer can have many systems to test and it is possible for each of their systems to be on a different testing schedule.

I know we need at least 3 tables. A table for customers, a table for contacts, and a table for the compressor systems. One customer can have many contacts; and one customer can have many systems to track.

Some of our customers also have customers of their own (they are distributors for us). In the past we did not keep track of their clients compressor systems (they handled it on their own). But with FM 7, we want to start keeping track of when their customers are due for tests as well. However, I don't want to lump them in with our DIRECT customers because we need someway to know that they 'belong' to another customer. This is where I start getting confused on how to design the solution.

Would it be best to have another table (we'll call it "Their Clients") and then a completely separate table with 'Their Client Contacts' and then another separate table with 'Their Client Compressor Systems'? Or is there a way to keep track of all of this information in the original 3 tables I described? The 'key fields' involved are tripping me up. I hope this makes sense. Thanks for any help you can give me!

Kristine

[QuinTech]

Hi, Kristine. My rule of thumb, and i'm not alone in this, is that objects of the same type deserve exactly one table. In this case, i'd call "Compressor System Clients" one type of object, regardless of whether they are a distributor or not. So for that reason, i'd go with three tables.

If you want to add a dimension, you could assign each client record a distributor ID, and then populate that field with the appropriate ClientID for those who buy from a reseller. That ClientID would, of course, match the ClientID for the actual record of the seller. In other words:

CLIENTID   NAME   DISTRIBUTORID

--------------   --------    ---------------------

Cli001            Joe

Cli002            Sue           Cli001

In this case, Joe deals directly with you, and is a distributor for Sue, who does not deal directly with you.

My 2c.

HTH,

Jerry

[QuinTech]

Hi, Kristine. My rule of thumb, and i'm not alone in this, is that objects of the same type deserve exactly one table. In this case, i'd call "Compressor System Clients" one type of object, regardless of whether they are a distributor or not. So for that reason, i'd go with three tables.

If you want to add a dimension, you could assign each client record a distributor ID, and then populate that field with the appropriate ClientID for those who buy from a reseller. That ClientID would, of course, match the ClientID for the actual record of the seller. In other words:

CLIENTID   NAME   DISTRIBUTORID

--------------   --------    ---------------------

Cli001            Joe

Cli002            Sue           Cli001

In this case, Joe deals directly with you, and is a distributor for Sue, who does not deal directly with you.

My 2c.

HTH,

Jerry

[QuinTech]

Hi, Kristine. My rule of thumb, and i'm not alone in this, is that objects of the same type deserve exactly one table. In this case, i'd call "Compressor System Clients" one type of object, regardless of whether they are a distributor or not. So for that reason, i'd go with three tables.

If you want to add a dimension, you could assign each client record a distributor ID, and then populate that field with the appropriate ClientID for those who buy from a reseller. That ClientID would, of course, match the ClientID for the actual record of the seller. In other words:

CLIENTID   NAME   DISTRIBUTORID

--------------   --------    ---------------------

Cli001            Joe

Cli002            Sue           Cli001

In this case, Joe deals directly with you, and is a distributor for Sue, who does not deal directly with you.

My 2c.

HTH,

Jerry

[HonkyCat]

Thanks for your help Jerry!

Kristine

[HonkyCat]

Thanks for your help Jerry!

Kristine

[HonkyCat]

Thanks for your help Jerry!

Kristine