Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 No PK's in big ERP system

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 02:54:29
Yaks,

I've just started on a new project for a mid-sized (by Norwegian standards) pharmaceutical company, and they're using Movex M3 by Lawson which is a pretty big ERP system consisting of about 3300 tables. Now, the table design is somewhat weird...they have a pretty good index design but there is not one single primary or foreign key in the entire database! All tables have a clustered index and several non-clustered, but there simply are no pk/fk constraints at all. What could be the reason for such a design??

- Lumbago
My blog-> http://thefirstsql.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-09-11 : 07:01:24
Their developers feel that all data integrity should be done in the application rather than the database. Could be stupidity, arrogance, or the need to make it work on multiple RDBMS products. I'm sure they've looked at other ERP products and saw they didn't have them either, so why break tradition?
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 07:30:25
Hehe...or it may be legacy from back in the day when developers (still) had no clue what they were doing I have noticed that the clustered indexes are made using some nullable columns and I actually didn't think this was even possible so I guess I learned something new. The real bummer is that the clustered index is in fact the row identifier, but because of these nullable columns it's not possible to make it in to a PK, so I actually have to continue on this design in my project as well.

Greeeeat!

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-09-11 : 22:18:30
I have a theory that SAP was first available before RDBMS existed, and they've grafted their data structures onto them without redesigning to take advantage. This is based on my brief experience learning about SAP for a migration, and the design is utterly horrendous. It makes Sharepoint look like art in comparison. The only thing worse than the database design is the API.

Clustered indexes in SQL Server are automatically made unique if the underlying data isn't already unique by appending an integer "uniqueifier" to the values. This of course is added to any non-clustered indexes, and if they're nullable, the null bitmap is also included for every value at every level of the index. If you read Kimberly Tripp on SQLSkills.com she goes into a lot of detail on these side effects and their impact, and really explains clearly why you want a narrow clustering key that is naturally unique.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-12 : 02:42:56
I've fortunately never had the "pleasure" of working with SAP but this M3 system is not all bad. They do at least use their design philosophy pretty consistently throughout the application, despite how hard it must be to maintain logic in over 3300 tables which are ALL named in 6 characters and some of them being over 250 columns wide (yes, the column names are also all 6 characters). Did I mention that 32 of these tables have a 16-column clustered index? I actually made a query for fun to see the number of columns in the clustered indexes:
with cte as (
select distinct
TableName = OBJECT_NAME(ic.OBJECT_ID),
ColumnName = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.index_id = 1
),
cte2 as (
select
TableName,
ColumnsInClusteredIndex = count(*),
RowNum = ROW_NUMBER() OVER (ORDER BY TableName)
from cte
group by TableName
),
cte3 as (
select TotalCount = count(*) from cte2)

select
ColumnsInClusteredIndex,
TablesCount = count(*),
TotalCount,
Percentage = CONVERT(decimal(5, 2), ((count(*)*1.0) / TotalCount) * 100)
from cte2
cross join cte3
group by ColumnsInClusteredIndex, TotalCount
order by ColumnsInClusteredIndex desc


And the result? ->

ColumnsInClusteredIndex TablesCount TotalCount Percentage
16 32 3327 0.96
15 15 3327 0.45
14 23 3327 0.69
13 33 3327 0.99
12 38 3327 1.14
11 66 3327 1.98
10 69 3327 2.07
9 105 3327 3.16
8 165 3327 4.96
7 245 3327 7.36
6 322 3327 9.68
5 458 3327 13.77
4 535 3327 16.08
3 631 3327 18.97
2 407 3327 12.23
1 183 3327 5.50


- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-09-12 : 13:54:55
quote:
Originally posted by robvolk

Their developers feel that all data integrity should be done in the application rather than the database. Could be stupidity, arrogance, or the need to make it work on multiple RDBMS products. I'm sure they've looked at other ERP products and saw they didn't have them either, so why break tradition?



Lawson does - in fact - offer their products across multiple RDBMS products. I never had the pleasure of working with one of their products at a previous employer because it was running on Oracle. It had the same type of issues with PK/FK's not being defined in the database.

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-09-16 : 11:36:12
At least you don’t have those annoying FK violations and duplicate PK violations to deal with.

The 6 character name limit seems a little extreme. Must get pretty cryptic.








CODO ERGO SUM
Go to Top of Page
   

- Advertisement -