Database Design Question/Suggestions 1

[Crowley16]

Hi Everyone...

I've taken over a db schema which is to be used across multiple seperate locations, each with it's own set of isolated tills/operators/transactions...

The problem is, because of the isolated nature of the database, the transaction tables use a composite primary key consisting of StoreID, TillID, OperatorID, TransactionID along with it's own id number to maintain uniqueness on the "central" database used to house information across all locations.

As you are all aware, composite primary keys are not ideal, especially considering this is a heavily transactional system, and I would like to offer an viable solution, however the only option I can think of at the moment involves merging these ids into a SuperID against all tables, which doesn't seem to be much of an improvement.

Can anyone here think of a good viable solution to this problem?

Thanks

--------------------
Procrastinate Now!

 

[SQLSister]

Have you considered using a GUID as the id?

"NOTHING is more important in a database than integrity." ESquared

 

[Crowley16]

hmm, guid, good idea...

is a guid the same as a uniqueidentifier?

--------------------
Procrastinate Now!

 

[gmmastros]

>> is a guid the same as a uniqueidentifier?

Yes.

Declare @Temp Table(Id UniqueIdentifier)

Insert Into @Temp Values(NewId())
Insert Into @Temp Values(NewId())
Insert Into @Temp Values(NewId())

Select * From @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

thanks, will keep that in consideration, although it is kinda big, 16bytes for 1 uuid!

--------------------
Procrastinate Now!

 

[Crowley16]

might be worth mentioning, database sizes can be quite large, there's one which is 66GB right now, and table sizes well into the 100+ millions.

however concatenating 4 ints will take up the same amount of space I suppose...


--------------------
Procrastinate Now!

 

[gmmastros]

You shouldn't be too worried about 16 bytes. Most of the time, 16 bytes won't cause performance issues, which I suspect is your main concern here. In fact, your composite key is made up of 4 integers. At 4 bytes each, this is 16 bytes!

Personally, I don't see a problem with having a composite key. You do need to be careful though. As you know, Primary Keys have unique indexes associated with them (usually clustered).

The potential problem with composite occurs because of the key order. Right now you have... StoreID, TillID, OperatorID, TransactionID. If you look at the index (through enterprise manager), you'll notice that the 4 columns are listed as part of the primary key. By modifying the order that the appear in the list, you may improve the performance of some queries while causing other queries to perform worse.

Think of it this way... Suppose the 'first' column in the list is TransactionId. Now, suppose you want to get all of the transactions of a single store/till/operator. Since the first column in the index is transaction id, you will probably get a clustered index scan (instead of a seek) because the ordering is first by transactionid.

Make sense?

My suggestion is to examine the queries that use this table. Then, make sure that the order of the keys makes sense. If most of the queries have a where clause that includes StoreId, then I would suggest that should be the first column in the index. Also... If other queries don't include the StoreId but only include TillId (for example), then you will probably get better performance by creating an additional index on TillId.

You'll need to be careful that you don't have too many indexes on this table because it will slow down your inserts, updates, and deletes.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

be carefull with GUIDs, if you do a lot of inserts you will get pagesplits
unlike integers (if you use identity) which are in order guids are not

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

nice tips on indexing george, have a star...

the clustered indexing is a bit of an issue from a design point of view, i.e. when joining tables, you have to join 3/4 seperate columns instead of just the one.

also, doesn't inserting into clustered indexes also cause page splits?

as for the querying analysis, I've been planning to put in a request for a trace on a prod server for ages, but it keeps getting delayed...

I was hopeing there would be some simple solution which I have missed to solve this.

--------------------
Procrastinate Now!

 

[SQLDenis]

>>also, doesn't inserting into clustered indexes also cause page splits?

not if you are using identity since it will be inserted at the end not in the middle
That said SQL server 2005 has newsequentialid which doesn't have this issue

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

eh, firstly, I believe the ids are generated programatically on the front end so no identities apply.

secondly, the id values actually come from other tables, i.e. foreign keys, so cannot apply identity in this case.

thirdly, the pk is composed of 4 seperate columns, identity can only exist on one column, therefore, the other 3 columns will certainly not be appending at the end.

--------------------
Procrastinate Now!

 

[SQLDenis]

eh, firstly, I believe the ids are generated programatically on the front end so no identities apply.

as long as they are in the order of the clustered index that is fine

so if you have id 34567 and the next one is 12333 and then 33333 then those have to be inserted in pages already filled with data, if you fillfactor is high then there will probably be no space to insert the row and the page will have to be split, this is also what causes fragmentation

thirdly, the pk is composed of 4 seperate columns, identity can only exist on one column, therefore, the other 3 columns will certainly not be appending at the end.

as long as the first value is in order then the whole row will be inserted at the end. if you have a lot of duplication in the first value by having a lot of different values for the other 3 values you are not as bad off if the first key would be unique and not inserted in order

Also keep in consideration what the WHERE clause in searches will look like, create your index accoring to that

The problem is, because of the isolated nature of the database, the transaction tables use a composite primary key consisting of StoreID, TillID, OperatorID, TransactionID along with it's own id number to maintain uniqueness on the "central" database used to house information across all locations.

Can this new id be an integer (2 billion rows) or if that is not enough a bigint? it will be smaller than a GUID/UUID


These 4 columns

StoreID,
TillID,
OperatorID,
TransactionID

How are searches done? the ones which are frequently omitted put those at the end of the index

Experiment and see what works best for you

Soemtimes reordering the columns in an index can make a BIG difference





Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions