Top 10 Tips For Database Design

The following are some of the tips that can help ensure that databases created that can be easily exported and manipulated with the minimum of difficulties.

  1. Develop A Prototype – Significant time can be saved by creating the structure in a simple desktop database (such as Microsoft Access) before finalising the design in one of the enterprise databases. The developer will be able to recognise simple faults and makes changes more rapidly than would be possible at a later date.
  2. Split database structure into multiple tables – Unlike paper-based structures, databases do not require the storage of all fields in a single table. For large databases it is useful to split essential information into multiple tables. Before creating a database, ensure that the data has been normalised to avoid duplication.
  3. Use understandable field names – The developer should avoid field names that are not instantly recognisable. Acronyms or internal references will confuse users and future developers who are not completely familiar with the database.
  4. Avoid illegal file names – It is considered good practice to avoid exotic characters in file or field names. Exotic characters would include ampersands, percentages, asterisks, brackets and quotation marks. You should also avoid spaces in field and table names.
  5. Ensure Consistency – Remain consistent with data entry. If including title (Mr, Miss, etc.) include it for all records. Similarly, if you have established that house number and address belong in different fields, always split them.
  6. Avoid blank fields – Blank fields can cause problems when interpreting the data at a later date. Does it mean that you have no information, or you have forgotten to enter the information? If information is unavailable it is better to provide a standard response (e.g. unknown).
  7. Use standard descriptors for date and time – Date and time can be easily confused when exporting database fields in a text file. A date that reads ‘12/04/2003’ can have two meanings, referring to April 12th or December 4th, 2003. To avoid ambiguity always enter and store dates with a four-digit century and times of day using the 24hr clock. The ISO format (yyyy-mm-dd) is useful for absolute clarity, particularly when mixing databases at a later date.
  8. Use currency fields if appropriate – Currency data types are designed for modern decimal currencies and can cause problems when handling old style currency systems, such as Britain’s currency system prior to 1971 that divided currency into pounds, shillings and pence.
  9. Avoid proprietary extensions – Care should be taken when using proprietary extensions, as their use will tie your database to a particular software package. Examples of proprietary extensions include the user interface and application-specific commands.
  10. Avoid the use of field dividers – Commas, quotation marks and semi-colons are all used as methods of separating fields when databases are exported to a plain text file and subsequently re-imported into another database. When entering data into a database you should choose an alternative character that represents these characters.

And DAMN it’s been a long time since I’ve put something here. I’m not forgetting about the site… just the new job has been taking quite a bit of time out of my leisure activities, this website is one of them. I made quick work of the dozens of spams that got through the filter and updated WordPress.

Also, another problem is I have started using Linux. I’ve been running a mix of Linux and XP at home lately to get a feel of the environment and hope to shift entirely to Linux on the day.

Let’s hope that the next post isn’t so far out. In fact, I promise the next thing I write will be sooner rather than later. 😉

Advertisements

JDBC Prepared Statements

A Prepared Statement forces a SQL statement to be set to the database immediately where it will be precompiled. This means that for subsequent calls, the SQL statement can just run the Prepared Statements’ SQL statement without having to compile it with every call. A Prepared Statement is most useful when using a SQL statement that accepts parameters that may change with every call. I’ll demonstrate this technique using a MySQL database.

Below is a method that sends a Prepared Statement to the database with two parameters: “userId” and “password”. Notice that it calls the getConnection () method from a class instance called dataUtil. We’ll talk about this class in a second for completeness.


    public ResultSet viewItem (int journalId, int userId) throws SQLException {
        ResultSet rs = null;
        String sql = "SELECT journal_id, date_added, journal_text FROM journal" +
                                                                 "WHERE journal_id = ? AND user_id = ?";

        Connection conn = dataUtil.getConnection();

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1, journalId);
        preparedStatement.setInt(2, userId);

        rs = preparedStatement.executeQuery();

        return rs;
    }


As I mentioned above, I wanted to show you how to use the getConnection method, because after all, if you can’t connect to a database you can’t execute a Prepared Statement. It will return an open Connection object for our Prepared Statement to use. The SERVER and DATABASE constants you would replace with your server address and database name, and the USER and PASS constants would obviously be your database username and password. This method requires the MySQL Connector/J JDBC driver. You can grab this at http://www.mysql.com


    public Connection getConnection() {
        try {
            Class.forName("org.gjt.mm.mysql.Driver");

            Properties connProp = new Properties();
            connProp.put ("user", USER);
            connProp.put ("password", PASS);
            connProp.put ("useUnicode", "false");

            Connection con = DriverManager.getConnection("jdbc:mysql://" + SERVER + "/" + DATABASE, connProp);
            return con;

        }catch (ClassNotFoundException cnf){
            return null;
        }catch (SQLException ex) {
            return null;
        }
    }

Notice that we first declare a variable to represent the SQL statement, and assign the SQL to it:

                    String sql = "SELECT journal_id, date_added, journal_text 

						FROM journal WHERE journal_id = ? AND user_id = ?";
The values that we will be sending to the database are represented by "?". Next we call our

getConnection () method to get our connection object and then define our Prepared Statement,

and then append our parameters to the Prepared Statement with these lines.
			preparedStatement.setInt(1, journalId);

 		preparedStatement.setInt(2, userId);
Notice that you should use the correct set method depending on the data type of the field you are

assigning the value to (see References below). All that's left to do is to execute the Prepared Statement

and to return the result set.

Reference: java.sun.com


Blogged with Flock