Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Primary Key
ajayjain
Member Posts: 119
Hi,
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)
DOC001 D1
DOC001 D2
DOC002 D1
DOC003 D1
Table 2
DOC001-D1
DOC001-D2
DOC002-D1
DOC003-D1
which one will you prefer. of-course I will have document no. in table 2 as well but not part of primary key.
Thanks
Ajay
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)
DOC001 D1
DOC001 D2
DOC002 D1
DOC003 D1
Table 2
DOC001-D1
DOC001-D2
DOC002-D1
DOC003-D1
which one will you prefer. of-course I will have document no. in table 2 as well but not part of primary key.
Thanks
Ajay
Ajay Jain
UK
UK
0
Comments
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
DOC001-T01
DOC001-T02
DOC003-T01
in both cases FINDLAST will be used with INCSTR for next no. with document no. filter.
Thanks
UK
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.