I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this level of table design so advice would be greatly appreciated.
The table will be queried mainly on looking for a specific transaction (TrxDate, StoreNo, RegNo and TrxNo) or summarising data by TrxDate/ StoreNo.
Do my indecies look correct? Should the UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo index be a primary key? Is it worth adding another index just to cover looking for specific transactions (same a UX but without SeqNo)?
This is going to be a key table as the database grows and I really want to avoid having to change things round once the table has grown significantly.
CREATE TABLE sfs.tbl_POS_eCommTrx (TrxDate int NOT NULL, StoreNo smallint NOT NULL, RegNo smallint NOT NULL, TrxNo smallint NOT NULL, SeqNo tinyint NOT NULL, SKN int NOT NULL, Pcs smallint NOT NULL, Retail decimal(38,4) NOT NULL, CorrTend numeric(1,0) NOT NULL, OrderID int, UID int NOT NULL identity(1,1), DateStamp datetime Default(getdate()) ) CREATE UNIQUE INDEX UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo,RegNo,TrxNo,SeqNo) CREATE INDEX IX_POS_eCommTrx_TrxDate_StoreNo ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo)
Thanks in advance.