ArtenSUITE: Part 3: Database Design

ArtenSUITE
A Development Journal
Part 3: Database Design

I spent most of today working on the database structures and design issues, deciding on what conventions I will be following regarding naming, table designs etc etc.

For example, as all the modules are designed to support multi companies, I have build a foreign key into each table which links to the company record. Access to the data from within the application modules will purely be based on the context of the company you wish to work with, rather than logging into a seperate database as is commonly done. Global constants will be stored with a record linked to the company record.

I also want to support simple features that users consider necessary but are not seen often in commercial applications. An example would be allowing the same contacts record to be used against both customers and suppliers, and then allowing that contact record to be moved to another customer or supplier if the individual changes jobs. This is a common requirement but is not widely supported.

Jeff Atwood recently blogged about database structures and whilst I agree with him to a certain extent, I will say that I much prefer to start with a solid normalised database design and only denormalise wherever *absolutely* necessary. This can lead to tables that look complicated. However I believe that creating appropriate views can help the database structure become much more meaningful when it’s time to report or query the data. I also disagree that normalised databases have performance issues ... of course there are times where a normalised database will perform slower due to the joins, but on the whole the quality and reliability of the database should make up for any performance shortcomings. Besides, there is normalisation and there is normalisation. I like to think that my database designs are optimum for the type of application that I am developing.

Other decisions I made today were to do with the data storage types for the database columns. For example each table will have a primary key, this primary key will be tied to an Oracle Sequence so that it is guaranteed to be unique and not null. I will use a Number type with a scale of 14 and precision of 0 to store this value. Customer and Supplier codes will be Varchar2 12, company names and address lines will be Varchar2 50, currency values will be Number with a scale of 12 and a precision of 2.

Tables will be named PRIMARYMODULE_TYPE_SUBTYPE for example the customer address table would be named BAS_CUS_ADDR. Where BAS means that the table is primarily used and maintained within the module BASE. Primary columns will named XXX_PK, customer primary key will therefore be CUS_PK. There are lots more rules and conventions but you get the general idea :-)

An idea I have implemented here is one that I have carried over into every database design for the last 15 or so years. It is probably not necessary nowadays but - belt and braces ! I have created an additional column in every table called (for example) CUS_999. This contains a copy of the primary key. The idea here is that this can be used to rebuild the relationships between the tables if the primary key every got corrupted. This has saved my bacon several times over the years ! It can’t do any harm and disk storage is cheap.

So lots of thinking and designing today. In addition I got to play more with Oracle XE. Really, if you have never tried it you should, it’s great. It’s simple to use, has a cracking browser based interface for setting up and managing the database and it installs quickly and simply. This does not feel like ‘traditional’ Oracle. I’m very impressed.

Aside from an hour or so trying to solve my Java on Windows issue (this is a real pain, I know the solution is simple and I’m just being stupid) the only other coding I have done has been to create a properties window for ArtenBASE. This is a useful view for the main properties of the application, especially when developing. Below is a screenshot of the properties window.



That’s enough for today. Tomorrow is my last day working before my holiday (yep, another one) this time I am staying in the country so will have iPhone, MacBook Pro and Vodafone Broadband Card so although I won’t be able to get much work done I can at least answer my emails.

Feel free to comment if you have any suggestions, observations, criticisms etc :-)

"Millions long for immortality who don't know what to do with themselves on a rainy Sunday afternoon." - Susan Ertz