I'm working on an invoicing system that has to allow mutliple users ie... the system can be used by several different unrelated companies to produce their invoices.


One think i'm unsure about is the best way to create some of the primary keys:

For example

On a single user system an invoice would have a natural primary key of its invoice number, however, on the mutli-user system this is obviously not the case as two users(companies) may well have the same invoice number.

My first thought was to have a primary key made up of the two fields user_id & invoice number

Then i thought i might be better to have an internal invoice id as a primary key (autonumber, i'm using mysql) and then add a unique index to the user_id & invoice_no to prevent an individual user creating two invoices with the same number...

The idea of having a primary key made up of two fields always seems to be a bit of a hassle even though i think it makes more sense logically...

I wondered if anyone had any thoughts/comments on the two approaches..

many thanks

j

I'm in favor of using the surrogate key (autonumber) as the primary key, and the combination of either company and invoice or the combination of user and invoice for the unique constraint.

I try to always have a PK that is used solely by and for my system. The database, app server, web server, etc. use my PK. The user never even sees the PK if I can help it, and they are NEVER allowed to change it.

-PatP

yes, that sounds like good sense to me...

thanks for the reply

Another approach might be to create an entire set of new tables for each user.

Any thoughts/comments?