I am looking at a database design and would like some comments on what some "competent" database people might think - any suggestions, etc. The tables have been 'normalized' and there are some 300 tables. The application is a club membership system and will maintain member data such as name, address, attendance, monthly payments, etc. This is an "improved" system that was functioning with 8 tables. I appreciate any suggestions. I can't get the paste to work with a screen shot -so- I'll just type a few.
Thanks,
Paul
dbo.Account
AccountGUID (PK, uniqueidentifier, not null)
AccountTypeGUID (FK, uniqueidentifier, not null)
AccountIsActive (bit, not null)
AccountAllowMultipleMembers (bit, not null)
AccountInterestRate (numeric(9,14), not null)
AccountAccountLimit (money, not null)
AccountCurrentBalance (money, not null)
AccountGracePeriod (int, not null)
AccountIsDeleted (bit, not null)
AccountLastUpdated (datetime, not null)
AccountChangedBy (nvarchar(32), not null)
dbo.AccountClosed
AccountClosedGUID (PK, uniqueidentifier, not null)
AccountGUID (FK, uniqueidentifier, not null)
AccountClosedDate (datetime, not null)
AccountClosedIsDeleted (bit, not null)
AccountClosedLastUpdated (datetime, not null)
AccountClosedChangedBy (nvarchar(32), not null)
dbo.AccountPaymentSchedule
AccountPaymentScheduleGUID (PK, uniqueidentifier, not null)
AccountGUID (FK, uniqueidentifier, not null)
AccountPaymentScheduleDate (datetime, not null)
AccountPaymentScheduleAmount (money, not null)
AccountPaymentScheduleIsDeleted (bit, not null)
AccountPaymentScheduleLastUpdated (datetime, not null)
AccountPaymentScheduleChangedBy (nvarchar(32), not null)
First, why are you using GUIDs? GUIDS have a definite negative impact on performance. We discussed this a little last week here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1492619&SiteID=1
Also, what is your "Numeric(9,14)" column? This is not valid. look what happens here:
Otherwise might be ok. Somebody else check me?|||Sorry - I transposed that one - it should have been 19,4. This is software that we bought from another company and I have been trying to convince them that this database is way over normalized, that GUIDs were a bad idea and are the cause of very poor performance. Most inquiries to this system take slightly longer than forever to return. This is a stand alone system that most users run on a single PC at each facility. The company that wrote this software insists that it is for security purposes and that the data is "encrypted" to protect the privicy of the individuals. Of course when I open the table data the only thing that could be considered to be encrypted is the GUID keys - the data is in very plain english. I was hoping that I could get a number of negative comments about this approach to show them why this is a bad design.declare @.what numeric (9,14)
/*
Server: Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.
*/
Thanks for your input,
Paul
|||
The biggest impact from GUIDS is the data bloating that occurs. This is especially a problem for indexes and foreign keys. In your "AccountClosed" and "AccountPaymentSchedule" tables you have the GUID key and the GUID migrated GUID foreign key. Your keys consume more space than your data. And if you consider the last three fields as being "auditing" and "book keeping" columns you have a lot of data "overhead" with only a tiny amount of "primary data".
Yes, this is going to hurt performance.
|||Thanks Ken. Trying not to be too negative - I think this is one of the poorest designs I have ever seen by someone that obviously had very little database knowledge. I simply don't understand why anyone would not seek guidance and better information before building a monster. Especially when there is so much good information available if one just looks. Thanks again for your comments.Paul
|||
Well, good luck in getting it changed. I can tell you for sure that similar experiences of trying to change bad practice is one of my biggest sources of frustration. There is nothing like being asked to draft a document of "best practices" and then finding out that you have to abandon some of the cornerstones because some bad practices are so entrenched that there is no hope of getting changed without incurring major expense.
My e-mail is publicly available; if I you would like any other help you can always e-mail me and I will provide what help I can.
Trying to make a difference.
Kent
( Just click on my name for my E-mail address in the POST information; you don't need to go to the company website. and the "_dot" is not part of the address; it is a catcher to avoid potential spam. )
|||Thanks Ken, I was not going to bother you with a direct email but, I did try to go to fnf_dot.com to see what it is and I get - Problem loading page / Server Not Found.Paul
