Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm making an employee information database. I need to handle employees that are separated from the company. Should I a. set up a query with a macro to send separated employees to a separate table, or b. just add a flag to the single table denoting separation?

I understand that it's best practice to take choice b, and the one reason I can think of for this is that any structural changes I make to the table later will have to be done in both places. But it also seems like setting up a flag forces me to filter out that flag for basically every useful query I'm going to make in the future.

share|improve this question
When you say 'separated', do you mean employees who have left the company? – Linker3000 Mar 15 '11 at 21:25
@Linker3000 Yes. – Joe Mar 15 '11 at 22:59

That's what views are for.

Use one table with a flag, then create 2 views - one selecting for the flag set, and one selecting for the flag unset. The views don't need to contain the flag itself - only the columns you actually need for that subset of data.

That way you get the ease of use of separate tables with the optimizations of a single table.

Here's a tutorial to help you get started with views.

share|improve this answer
Can you create indexes on views? Because I argue two tables in my answer purely for performance reasons. – Justin Dearing Mar 16 '11 at 20:52
You create the indexes on the parent table. If you include the flag field in the index then it is as if you have the index on the view. – Majenko Mar 16 '11 at 20:56
Yes, but that will make wider and longer index than the same index on two tables without the flag field. – Justin Dearing Mar 16 '11 at 20:57
But will make it far easier and more efficient for any queries where you want to reference all employees. – Majenko Mar 16 '11 at 21:01
Agreed, but that seems like something you would rarely want to do. Also, the question is about MS Access, so its not going to scale to the point where such archiving and partitioning is really that benificial. – Justin Dearing Mar 16 '11 at 21:03

NOTE: It seems you can create indexes on views. This would lend weight to one table and two views.

Its a matter of performance versus relational correctness. The "purest" way to do this would be one table. However, two tables should perform better in most situations. Even if all your queries are against a multi valued index on this table begining with side flag, querying against one of two smaller tables would be quicker. This is because your indexes would contain one more field and more records (a wider and longer index).

If you ever needed to query both sets of data, you could do a UNION. That would be slower. However, I assume these operations would be rare.

share|improve this answer
The cited article has nothing at all to do with Access tables, which is, I believe what the original question is about. However, it is, in fact, possible to have temporary indexes on recordsets opened with ADO, or, alternatively, you can index a table in a DAO transaction, query the data and not commit the change. I wouldn't recommend either of these approaches, but they are, in fact, possible. – David W. Fenton Mar 17 '11 at 3:07
I really don't at all understand the point here about having another field in the index. It wouldn't be part of the PK, it would just be an additional index (if that). In fact, a flag field is going to be sparsely populated (really, two values, max, unless you allow Nulls, in which case it could be three at most, though I can't figure out what Null would mean in this case), and many people recommend not indexing fields with low cardinality (like a Boolean). However, experience with Jet/ACE has taught me that indexes on Boolean fields do provide major performance enhancements. – David W. Fenton Mar 17 '11 at 3:09
...all that said, I can't imagine how this would be a big deal. If you were concerned, you could maintain a second table linked to the main table, and that would have a record only for the separated employees (or vice versa). This would take care of the indexing question, at the cost of a JOIN (and an expensive outer join when you wanted all the records and wanted to know the employment status). I just don't see how this single-field index is going to be of any significance in a data set that is 1. appropriate for Jet/ACE and 2. represents actual employees (how big is the company?). – David W. Fenton Mar 17 '11 at 3:11
...that is, it could only be an issue with millions of records and I don't know of any companies with millions of employees! I know of very few with 100s of thousands, which is well within the limits of Jet/ACE's capabilities to store and retrieve efficiently and quickly. – David W. Fenton Mar 17 '11 at 3:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .