best practices--multiple field unique index

[lscheer]

After reading through a number of posts here about the benefits of using an autonumber as the PK and setting a unique mulit-field index with other fields which I had thought of using as the PK, I have decided to go with the autonumber usage...however, I am wondering about the benefits of keeping my other fields separate. Basically, there are two fields one alpha, one numeric. The Numeric field should increment by one independently within each alpha charachter (i.e. AQ1, AQ2, AQ3, SW1, SW2...). With Pat's advice, I have also figured out how to make the increment work correctly, but she also recommends that the two fields are kept separate. As I think about and fiddle with my db, it seems like it would be less trouble (re-writing queries, etc.) to make them one field and set up the functions to increment within that field.

any more advice on best practices here? (and why)

Thanks !!!

 

[FoFa]

I am not sure about Best Practice, but you have to decide what is best first, from a data integrity standpoint. After that it becomes a toss up between ease of user (app user perspective) vs design and maint. issues. In your case, if no one but a developer see's the field as anything but AQ1 or AQ2, it might make more sense to keep it as a single field. If it is thought of as one field to everyone, it may make sense to keep it as one field. If the prior is true AND you have to concantinate these two fields in every query you do, it may make more sense to make this one field. After all each query is incurring overhead to do this number to string conversion and concatination. So then how do you handle the field if it is one field. I would write a function that would take something (either a complete field or just the alpha prefix) and calculate the next usable value and return it. Once this is designed and perfected, adding new values would be simple, and all your other processes can rely on a static, single value field. But this needs to be decided by the developers in the know on the data, how it is input and used.

 

[Mark Wild]

Having spent much time recently playing around with this issue, I agree with FoFa that from a development perspective, if the data is only ever used concantonated, why seperate it only to have to spend time putting it back together.

One issue that has been raised re this subject, is that of a multiuser envionment. If you create your own ID, think carefully about when you assign this number. If you do it as the user opens a data entry form, will another user trying to input be given the same id (assuming you use a max of the value in a table). What would happen if a user say goes to lunch whilst in the middle of data entry? What Id gets created for the enxt input.

You therefore need to either error trap for the possiblity of duplication of a PK or to assign the record ID as the user saves the data.

Hope this makes sense.

 

[lscheer]

Thanks for the suggestions...I'm happy to hear these responses since I agree...why create extra work for myself on the backend that then has to be figured out by someone in the future?

Thanks again!