Just a quick question.
I need to create a table which will be heavily used every day by more than 100 users.
Table will have Document No. and Task No. as major fields
Should I create a table with 2 fields as primary key or 1 field
Table 1 (primary key on 2 columns)
Document No. Task No.(must be code data type)
which one will you prefer. of-course I will have document no. in table 2 as well but not part of primary key.
However, combining fields does not make sense from a performance perspective.
If you insert data without filtering for line numbers and read data using the get command the SQL row locking will do what it should. Locking on row level.
We have lots of performance issues so I am little bit cautious to design tables.
Document no. will stay in both case as a seperate field, i need to decide should I include into primary key or not
Task no. will have to be code field and currently it is based on no. series and it is primary key
so for every new record regardless of document no., it is TSK001, TSK002...TSK4999...
This is most important key area in the whole database.
So I am changing this so users will be on different pages and will not lock others
in both cases FINDLAST will be used with INCSTR for next no. with document no. filter.
Numberseries and findlast/incstr will lead to locking. Also with this type of PK you will need space in the key regarding fill factor.
This is regardless of combining the key or not.
My advice is to try to get budget to hire a performance specialist/database designer. It will cost some money now but save tons of money later.