As I said earlier I hope to commercialise this application, therefore I need to make it multi-tenanted.  That’s pretty easy.  Firstly I created an organisations table – simple for now – just an ID and a name.  The record IDs are automatically created and are given a sub-type of “automatic identifier” – this means every time a record is added the database engine will automatically give it a new ID.  This works great and this ID is available in code immediately after the record is added which is a double bonus as that’s not always the case.  The IDs are numeric (4 or 8 byte) – for me this is fine as I don’t plan to allow for any offline working that requires data synchronisation – my app will be the single point of online data entry into the database.  If I did want data synchronisation then using GUIDs would probably be a better solution for the record IDs.

Each table I then create has an OrganisationID in it.  Even when I have child tables linked to a parent table that has the OrganisationID I still always put the OrganisationID on the child table – even though not really necessary.

I also put a Deleted boolean flag on each table.  Whenever a user deletes a record I set this flag to 1.  I have a separate table called DeletedItems – whenever I “delete” a record I add a record to this table – the table contains details of the table the record was deleted from, the record ID, user, date etc.  This enables me to have a neat utility where a superuser can view a list of all deleted records and can choose to permanently delete or undelete – the code is all very simple.

I don’t setup the links between the tables in the Analysis.  There are a few reasons for this:

• I find it confusing.
• I prefer to manage the referential integrity myself from the code.
• When I create Queries I prefer to define the joins myself and then take a look at the resultant SQL code to make sure I’m happy – I’m no SQL expert by any means, but I feel happier doing it this way.

Using the deleted flag means I have less to worry about from an RI perspective from the general front-end.  Of course if a user permanently deletes a record then I perform the RI checks.

Be careful when adding foreign keys to tables as it tends to recognise them as keys and sets them up as keys and defaults them to unique which is often not what you want – you need to remember to change it to “key with duplicates” or “not a key”.

I don’t create many keys myself initially.  Whenever I create a Query there is a useful feature to optimise the query.  It will suggest any additional keys on the tables and will even set them up for you.

Next I’ll cover how I access the data from the application.

• Gavin Webb - Building my WEBDEV Application