SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 No PK's in big ERP system
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 09/11/2013 :  02:54:29  Show Profile  Reply with Quote
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

USA
15635 Posts

Posted - 09/11/2013 :  07:01:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Norway
3271 Posts

Posted - 09/11/2013 :  07:30:25  Show Profile  Reply with Quote
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

USA
15635 Posts

Posted - 09/11/2013 :  22:18:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Norway
3271 Posts

Posted - 09/12/2013 :  02:42:56  Show Profile  Reply with Quote
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

USA
768 Posts

Posted - 09/12/2013 :  13:54:55  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 09/16/2013 :  11:36:12  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 09/16/2013 11:37:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000