Prevent duplicate dates

[Solo712]

One thing not mentioned in this thread is the inclusion of FirstName and LastName fields in the table. As the OP said that names could occur multiple times with different dates it would suggest that they would be better to normalise the names into a related table and record an ID in the junction table.

That would not only reduce the storage requirement but significantly reduce the cost of a compound index.

You are right in that in real life you would not see the LastName and FirstName in this kind of a table. Most likely that dateWO-thingy in the table would relate via some ID to another table where the protagonists' personal attributes were kept.

We might also consider the performance of the DLookup validation. I do know for sure that the performance of a DLookup is vastly superior on an indexed field.

I would expect that the search with DLookup would be slower than the search with indexed field. For obvious reasons. But that finding would be overshadowed by other considerations. For one, the Last Name - First Name pair itself would be a better candidate for a compound index in a normalized setting, so leaving out the date as a residual predicate would probably not mean a sequential search through the recordset. Second, the speed of the search needs to be balanced by the DML performance costs and the other thngs I mentioned in my last reply to Pat. On the whole I would definitely opt for the lookup option as a policy of setting up business rules. Not saying that there can't be exceptions to the rule. Frequency of searches would be one strong argument to counter with. But in this instance, I am sure, you can see, the unique compound index is used primarily to exclude an insert of a record into the recordset ! You would have practically no other use for it than finding whether a particular LastNameFirstName has entry for a particular date. If you have LastNameFirstName compound index in place there is practically no value of having also a LastNameFirstNameDateWO index, since the first index will restrict the date entries for the user in the WO table (which you will know are unique since you enforced that rule on insert). And obviously - everybody missed this also, the design with the unique index here would disallow not just duplicate dates for a user but also fail in its function if two John Smith'es were to show up in the set ! As would the lookup, btw.

I don't know if the same would be true for a compound index but the developers who created the engines put a lot of effort into optimisation and I would not be at all surprised if the engine did indeed use the compound index when appropriate. If so then the performance of the validation would be enhanced by the compund index.

Not sure what you are saying. I know for fact that Oracle optimized data block searches specifically to avoid the need for indexing.

I do agree that indexes should be used judiciously. However the ultimate protection to data integrity offered by the index is exactly that kind of judicious use.

That does not make sense either. "Judicious use" of index presupposes
a set of considerations to go by when deciding whether to use one or not. If indexes were really what you claim them to be then it would be "injudicious" not to use them whenever. But indexes are not that; they are a tool used mostly to speed up searches in the database. Other than the primary key to ensure addressability of records, indexes do not provide "ultimate protection to data integrity". That is a strange consensus to which people on this thread talked themselves into. It would not withstand scrutiny among knowledgeable people.

As has already been demonstrated, the potential for oversight is real.

That is a patently silly way of arguing. Yes, I admit it, I missed a quote in the quick and dirty example of the code. I quickly fixed it. Then someone came and made a big hullaballoo about few trivial issues which are fixable all in under a minute. None of the objections to the code had anything to do with the demonstrated method. Of course, people make mistakes, I do, Pat Hartman does, and you can bet your bottom dollar that the Jet engine was a full of bugs before they were fixed. It's not an argument for anything. That I believe a lookup function to be more appropriate in this setting does not mean I hold the db designers in contempt. I simply think that all things considered, it is a more appropriate and efficient way of implementing a business rule in this case than the one offered by an index.

Best,
Jiri

 

[Solo712]

Your quote about unnecessary indexes is also misguided...

since an index that enforces RI isn't unnecessary just because you could write code to do it yourself. If I wanted to, I could write code to replace Jet. I have a pretty good background in low-level coding so technically I could do it. It would probably only take a couple of years working alone. But why would I want to do that? To prove I'm smarter? Take a look at my original post which offers the reason I think the index is superior to code and address that if you want to continue to argue.

Pat, I asked you for some sample of expert opinion that would support your contention. Nada for that. That you think you could re-write the Jet engine is not an argument for anything. Neither are the reasons why you don't. For someone very sensitive about personal criticism, you really are quick to personalize issues and project your own intents into other people. I certainly would not make claims - as you do here - that I could do something that I have no way of proving I could do. I guess I don't have enough hubris for that !

Best,
Jiri

 

[Pat Hartman]

Bishop has never returned or commented on this thread. It would appear that none of this "discussion" has been helpful so let's just stop now before it gets nasty. Solo has his opinions and is sticking to them.