Hi Nukers,

I've just been poking around the database behind DNN 6, and have noticed that some basic database design principles are missing, or poorly implemented.  Here's a few.

TabModuleSettings.SettingValue - A varchar column that can be used to store referential links back to the file system via an entry like "FileID=21323".   Seems to store all sorts of data here, including binary (true/false) values.  Using a text field to store different types of data is a really, really bad practice.  This is not good for referential integrity, indexing, searching or coding. 

Lots of column name in lots of tables end in "ID" - presumably to store a foreign key.  There is rarely a foreign key constraint on such columns.  This is a time-bomb waiting to happen.  This is the perfect way to create lots of orphaned records.  Also makes it hard to track down relational links between tables.  Link the tables up via a key and reap the benefits of tight data integrity, query speed (index both ends of the joins).

Lots of meta-data / entity-attribute-value style (EAV) tables.  These are not good design and terrible for performance.  These are common when computer programmers design databases.  The amount of linking and joins you have to do just to get data is going to slow things down.  you cannot enforce data integrity or referential integrity.  One of the worse sins in database design.

There are other issues also.

Just wondering, is there a dedicated team that look after the database and oversee module development in terms of good database practice?  I think this would be good for the future of DNN development.  Perhaps a checklist that each module's database design needs to pass - not just at the end of development, but during the design phase also - where database design needs to be done right.

Hope this helps.  I say this in the spirit of product feedback and development.  DNN is a great platform that really needs a rock-solid database platform in order to grow and scale.

Rod

 

Hi Cathal, thanks for your response.

You can still have soft deletes with proper database referential integrity. You just need a "IsDeleted" bit field for each entity/table and switch it accordingly. It's a bit of additional work at the code level, but you won't run into all of the database orphans that DNN (or any database that doesn't have referential integrity constraints) will create. These will become a problem in the future - I 100% guarantee it. Also, you don't need to turn on the cascading delete option within the database - it's just there if you want the database to automatically do it. I think a lot of applications will handle the cascading "deletes" within code or similar.

The way I see it, application functionality should never compromise database integrity and best-practice design - the database has to be the last defense against bad data. Not implementing data integrity because of soft deletes is not right, and a lack of programmer knowledge.

Generic tables have their place, but generic database design will lead to generic performance, generic scalability and generic data integrity.

Also, please be wary of VarChar(MAX) columns. They have their place, but only use them if you need to. They dramatically affect indexing, page sizes, data storage and more. They should not be simply used as a replacement for varchar(x).

I hope one day DNN will have a dedicated database architect to advise, design and improve the overall performance of the database. A platform like DNN needs a solid platform under it.

Hope this helps.
Rod

Rod Weir wrote:
... Lots of meta-data / entity-attribute-value style (EAV) tables.  These are not good design and terrible for performance.  These are common when computer programmers design databases.  The amount of linking and joins you have to do just to get data is going to slow things down.  you cannot enforce data integrity or referential integrity.  One of the worse sins in database design.

I noticed that to when trying looking around in there.  I just cant wrap my head around it in the EVA style.  Why would a programmer want this? Seems way harder to grok.

..