Primary key and version control question.

[Agnes]

I got some table design problem and hope someone can give me advice.
I got an InvoiceTable with the primary key Invno, InvoiceChgtable will keep
the charges , noofunit, unitpirce.. etc, its primary key is using 'smallint'
with increment YES
Now, I need to keep each version of the invoice data in one history
database.
My problem is
1) Should I use store procedure to insert the previous verson data or
trigger rule ?
If using trigger rule ? How can I do that ? (I never use trigger
before )
2) If I want to keep the prevoius verson , How should I design the primary
in invoiceTable ?? Invoiceno with suffix? such as (INV0001-XX) XX is the
version ??
3) for some other tables, I got 3-relationship , parent , child and
grandchild, I got no idea how to design the primiary key in grandchild .

Sorry for a lot of stupid question.
Thanks in advance

 

[CJ Taylor]

Wow.

Ok, first things first.

Your trying to stack a lot into your PK there, which you don't really need
to do. Sure, it works great for indexing, but you can have multiple indexes
in SQL server to assist with that. PK's should be used just to indentify
the row, indexes take care of the rest.

Small int? could go a bit bigger, space isn't really a concern anymore.

Also, can't have text in smallints so INV0000-00 won't work...

Version control, you could have that as a column/field, not really necessary
in your key (again, your index can help here). and INV, well, you can set
up a forumla, but then again, you could just place that in a reporting
solution too..

So.. Updating history, yeah I prefer using triggers, easy to do and you
don't ever have to remember to do it again.. plus, ANYTIME that data is
changed, SQL handles it, regardless if it happened in your program or
somehwere else (i.e. enterprise manager). How to write them? It's just
your regular old T-SQL. you can call a SP if you wish and send it
parameters (which is a good idea) and the way they work is you get a copy of
the data within an Inserted, Deleted, or Updated RowObject within T-SQL...
and you can go from there.

Lastly.. your PK of your other tables. I generally try to design all my
tables with the same data type, just leads to less confusion later. Most
often I tend to use integers, and sql defaults to a rather large integer
value, so I don't worry about running out of em any time soon. Especially a
long...

HTH,
CJ




news:[email protected]...