Tables - Best Practice

[CCH]

Hi everyone, I'm looking for a few suggestions / or best practice situations on the following. I'm designing a HR system. As I have it now, everyone will be in one table - Canidates, Current Employees, and Terminated Employees. My question is that the best way, or should I setup seperate tables. There will be about 400 active employees at anyone time, there could be 30,000 canidates, and a few hundred terminated employee records. Do I keep these all in the same table, or seperate them into different tabels, and bring them all together on 1 screen? What do all the experts think. Thanks in advance for your help! :)

[David Jondreau]

It depends. How similar is the information between each of the three groups? If it's basically identical, one table will be enough. If the info is different, I'd go with four tables, one for each type and one for the info that is universal to all three goes in Contacts.

If you go with the latter, I'd also have a separate layout for each type.

[CCH]

Thanks for the quick response! The data will be the same and different. For example, when the record is a canidate record it will contain certain data, if the person is hired it will contain more data, if they are terminated or leave it will contain even more data. But we want to be able to search across all tables. Let's say I wanted to search for all records that have something in field one, because I want to serach current employees and canidates, and people who were terminated. Does this make sense? But I guess I'm worried about people getting confused if they are working with a canidate, a current employee or a previous employee :)

[Fenton]

Yes, this is a difficult situation. Because even though the data is much the same, the "uses" of it, and what people expect to see are different. Separate tables solve some of the problems, such as "separate uses and views," but require work-arounds for Finds across tables. One table solves Finds, but requires work-arounds so that views are restricted to the relevant type when needed; requires scripted navigations, and Finds that you do not want to cross types.

I think the best solution might be to use the David Graham method of separate tables for each, with a central table for all shared fields (such as names; in fact most fields in this case). That way you get dedicated tables and layouts for each type, using the central table for cross-table operations. It's a bit more complex to set up, but is fairly transparent in use. The central table's records are created automatically, no scripting is needed for that. Do a Find here for more discussion on this. It's kind of a new method. I mention it because it may be the best structure for what you want to do; and 'cause you say "Advanced" as your skill level.

[CCH]

AWESOME Post - Thanks so much for the info - I'll find more info on that here.