I have a database that will hold information about invoices and the invoice details. The invoice details come in flavors that provide various kinds of information, for example if the detail is usage detail, it would have InvoiceID, Amount, Quantity, Minutes, FromLocation and ToLocation, if the detail was tax detail it would have InvoiceID, Amount, and Taxing Location. There are around 10 different kinds of detail per invoice.

Some of the detail is low volume, but there are detail types that will have 2,000,000 detail records per invoice.

So I've designed a central invoicedetail table and sidecar tables. The central holds invoiceID, Amount, and Type, with a bigint identity column of detailID as the primary key.

Each sidecar row would hold the columns for that type that are different but would have the same primary key (but not as an identity).

Now my problem. If I insert records one at a time I can get the identiy key back from the central table and use it to update the sidecar, I'm afraid this won't perform, but I don't see a set based way to do the inserts and have the central and sidecar tables share the same primary key value. I've though of using guids, but besides being twice as big, I don't know how fast NewID() performs, and they are hard (for people) to deal with.

Am I misdesigning this? What other approaches can you think of?

I believe Marcus had some thread about guids and indexes which were bad with large tables, some time ago. Let me see if I can find that thread back.

edit: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3289

perhaps informative for your situation.

Thanks, I'd followed that when it came out, and reviewed it now.

I think my first main question is around the issue of sharing a primary key between two tables , where it is a identity field in one and needs to be retrieved to be inserted in the other. If I do the inserts one at a time I can do this easily (but slower) than if I could find a set based way to do it.