ArtenSUITE Part 5: More Database and Table Design

ArtenSUITE
A Development Journal
Part 5: More Database and Table Design

No programming today. Working entirely on database design and creating tables, foreign key constraints, indexes etc. So far I have 17 tables designed and setup, these are some of the core tables that will be needed by most business applications that I create as part of ArtenSUITE. The screenshot below shows the 17 tables:



Comments

As you can see I have used the Oracle ‘Table Comment’ feature to put a store a plain english description of the table in the database. This is done so rarely, and many times when working with legacy Oracle systems I have *really* wished more developers/DBA’s took advantage of this feature. Properly commented tables and columns mean there is often no need to refer to a data dictionary or technical document to understand the database structure of a system you have never seen before.

To use the comment feature in Oracle you use the following syntax:

SQL> comment on table TABLE is ‘TABLE DESCRIPTION’;

Comments can also be applied to views and even the columns in tables. Comments are applied to views using exactly the same syntax as tables (shown above). To apply a comment to a particular table column, use the following:

SQL> comment on column TABLE.COLUMN is ‘COLUMN DESCRIPTION’;

Security

Access to most facilities within ArtenSUITE will be able to be restricted to particular personnel. The design of this is one that I have used very successfully in the past. A table exists which contains the following information (select columns only shown)

        Security Action ID
        Security Action Description
        User Primary Key

Each section of the program that needs to be secure has a ‘Security Action ID’ assigned to it. When the menu is selected or the button is clicked to perform the restricted action a call is made to a function with the parameters being the User Primary Key and the Security Action ID:

        call check_if_access_allowed(User Primary Key, Security Action ID)
        if return 1
                enter the method, access allowed
                ...

the function that performs the check looks like this:

        sub check_if_access_allowed
                select security_pk from security_table where user_pk = [User Primary Key] and security_action_id = [Security Action ID]
                if row returned from select
                        return 1
                else
                        Show Message “Action to [Security ID] - Not Granted. Please see the System Administrator for Access ...“
                        return 0
                end if
        end sub

To grant a particular user access to a particular action it is only necessary to create (or duplicate and edit) a security record. As a developer it is only necessary to prefix every ‘action’ with a security id and a call to the security procedure. If a customer needs a particular action to be secured in future they can enter the security record themselves.

Oracle XE and Oracle SQL Developer

I used the ‘Application Express’ interface for many of my tasks today and it is quite good. Easy and pleasant to use. I couldn’t however get the ‘Edit Triggers’ to work and so I switched to Oracle SQL Developer. After getting the latest version I was surprised just how much this product has improved recently. The last version I used was ... iffy to say the least. It has been tidied up, made faster and slicker. I like.

Archivelog Mode

One of the first things anybody should do with an Oracle database is switch on Archivelog mode. There is not really any good reason to run with Archivelog switched off (especially in a production environment). I’ve never had a problem with this before but with Oracle XE I couldn’t get the SQLPlus command line client to work as expected. After consulting the instructions I was doing what was expected but no luck. In the end I had to logon from the windows Command Shell as such:

        c:\>sqlplus /nolog

        SQL>connect sys/letmein@127.0.0.1:1521/XE as sysdba

I was then able to do the following to enable archivelog mode:

        SQL>shutdown immediate;
        SQL>startup mount;
        SQL>alter database archivelog;
        SQL>alter database open;

and that was that sorted.

Customer Tables

For my customer table designs I have a main customer table and a few additional tables that hold extra information such as bank details, notes, addresses etc. It is normalised but not to an excessive extent, 3NF give or take :-) A screenshot is shown below: (The diagram comes from Application Express and is pretty bad as it doesn’t even show the direction of the links. Anybody know any good diagramming software for Oracle ?)



Summary

That’s it for a week or so, vacation beckons :-) As always feel free to leave your comments and opinions.

"The radical of one century is the conservative of the next. The radical invents the views. When he has worn them out the conservative adopts them." - Mark Twain

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business