I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.
CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT NULL, Type int NOT NULL, CustomerId int NOT NULL, -- etc )
I am migrating data from a legacy system into this table and there is a requirement that Collection and Delivery notes have their own sequential numbers.
I had previously implemented two sequence tables as
CREATE TABLE CollectionNoteSequence ( Id int IDENTITY(1,1) NOT NULL, NoteId int NOT NULL )
Id column is the unique, sequential Id for collection notes, and then the NoteId foreign key's to
It's getting towards time to do the final (real) data migration and this setup seems hard to work with.
Is there a way I could bin the two sequence tables and add a
NoteNo field to the Notes table such that
NoteNo would be sequential depending on the
Note.Type ? Is this a composite key or something?
The new table might look like
CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT NULL, NoteNo int NOT NULL, Type int NOT NULL, CustomerId int NOT NULL, -- etc )
and the data would look like:
Id NoteNo Type CustomerId 1 4000 1 123 2 4001 1 456 3 15123 2 789 4 4002 1 753 5 15124 2 741
I'm using MS SQL Server 2008.