The database for umbraco is a core part of the application however there are some design issues that should be addressed.

I can see the umbraco database has been developed by a developer and not a DBA. There is a lack of relationships, selection of the correct data types, constraints, indexes etc. Obviously the unbraco application handles the business logic which is fine and I do not want to get into a debate about where business logic should be enforced but there are some inefficiencies in the current design that are effecting the performance of the front end application and that are impairing scalability.

For example
Data types. We use umbraco stats, the stats table uses a nvarchar 50 data types for storing IP addresses, this is not very efficient (in-fact there are many nvarchar datatapes used throut the umbraco database where other data types could be used. I have seen this befoure developers use a data type they know will work and forget about the consequences). We have 3.5 million entries in our umbraco stats tables and the stats reports take nearly a minute to refresh I am a database guy and I know this should not be the case (100+ mb of IP addresses that need to be filtered by the by possibly has something to do with this!).

Application logic.
I created a bulk user import script for one of our applications. However it appears that there must be a property record for all members of a member type even if that property is null. Not a problem until a member type is updated leading to umbraco crashing when the script is run and a member property is not set. Incedential this is the reason why it is very slow adding new properties to a member type that has many users as Umbraco inserts many many records into the member property table, records that essentially contain no data! This is not a db issue but is related to application logic. If however the db did not allow property data to be null (using a constraint) then the developer would be forced to write their code a little differently. This my be a pain in the ass but would lead to a more stable, efficient and scalable platform.

Whats the problem all this can be fixed in the future
Yes and no. Some schema changes will cause no issues however there are some that would require umbraco core code re-writes. Also there also all those packages and controls that the community has created (Unbraco backup etc) that may run into issues with schema changes.

So what can be done
Is there a data dictionary for the umbraco database available? If so could someone please send me one. I could make a list of suggested changes for improving robustness and scalability though this would have to be done with the active involvement of the Umbraco core team.

Thanks for the response, I will be happy to help as I am keen to help this project out any way I can. I will mail you my user name.

Dear Alec Griffiths and All,

I have downloaded the umbraco source code (developers edition) and deployed it on my system. For database, I have used the db script bundled in umbraco package for publishing but I have found that there are some db objects missing in the script (e.g. stored procedure named 'umbracoAPIContentGetAll').

Would you please help me in getting the original db script for umbraco source code? Waiting your kind reply ...

Kashif Khan

Hi Alex!

How are you?

Yes, that would be lovely. The scripts are old and lacks relations as well. If anyone is interested in giving them a shot, you'll make me a happy man.

/n

---

Jeeeez, did I really start this :-)

Its been some time but I am keen to get on with this. Anyone interested please write to me alecgriffiths AT gmail dot com