Creating non-existent database tables in code

Question

Up to this point my company has had an aversion to databases and has stuck to storing all data in comma-separated files. I have run into a unique customer application that - I believe - would benefit from the use of a relational database. The application calls for storing 'summary' data concerning a manufacturing process and 'detailed' data on multiple sub-processes. The sub processes need to be linked to the process summary.

My question: Is it normal/appropriate to check for the existence of a table prior to writing to this table, and creating it if the table does not exist? This may lead to a larger question now that I'm typing this; if the database does not exist, should I be creating the database and any tables that it requires?

Answer 1

SubSonic SimpleRepository generaly handles this type of situation very well(except for foreign key relationships).

Generally i would tend not to create and maintain database and/or tables via code, as this is prone to blow up in your face.

Answer 2

Sqlite has a "create table if not exists" syntax for exactly this type of context. (check http://www.sqlite.org/lang_createtable.html ) Most libraries also have an option to create a new sqlite database file on connection if it doesn't already exist.

Answer 3

Normally I'd say that non-existance of a table implies a problem somewhere. Dynamically created tables are usually a sign of something going wrong and if they are standard tables (eg tblUsers) then they should always exist having been set up as part of a setup process.

The times I can think of that checking for existance makes sense is once you have got a certain distance in code development and you upgrade your database to add extra tables but want your code to work with this new design and the old one. In this case checking for existance of tables before trying to use them would make sense.

In general though you should know what tables are in your database and so not need to check for their existance. And I am totally lost as to why you would be trying to access databases that don't exist... Are you thinking about a lazy setup process of some kind where new clients (or similar) can be jsut added on the fly and their databases created? If so I'd have a single process to create and set up those new databases.

Answer 4

No, it is not normal to create tables in code. If you need a relational database, you will need to design it before use. Relational databases are not to be trifled with.

If you've never done this before, here is an introduction to the subject...http://www.databasedev.co.uk/design_basics.html

(Sorry if you know all this; I don't mean to patronise.)

Answer 5

With SQLite, creating the tables yourself is a good approach. You don't need to be making your installation instructions for your customers any more complex than they already are, and if the database is entirely internal to your application, they probably don't even care what format it is in, so long as your software is reliable and fast.

Firefox, for example, uses SQLite for its internal storage these days, and they never bother the user to create databases. And users like it that way. :)

But I wouldn't worry too much about creating the database immediately prior to referencing the tables: instead, I would place the creation logic at application startup or installation time, whichever is more appropriate. Where are the CSV files created in your application now? Are they created afresh, every single time they are referenced? Or are they created in initialization or installation routines too?

Answer 6

Relational DataBases allow easy share data.

You can use files when do´nt need share data.

DataSet serialization is a good option for store information on files. You maintain data structured like relational DataBases.

Xml Serialization is slow but you can use BinaryFormatter with:

RemotingFormat = SerializationFormat.Binary

This option makes fast and compact binary Serialization.

Code-Project includes a "Fast Serialization" article. But this is not standard.

Answer 7

You should know the structure of your data before writing the application.

Keep the data and application separate wherever possible.

Using a Data Access Layer also means that if you already have a Windows application (for example) and then want to create a web front end you can easily re-use the DAL.

If the data access is embedded within the application it's much more difficult to re-use that code.

Answer 8

The answer depends on the nature of your project.

If this is an in-house system with a single instance, it's reasonable to save yourself some development time and manually update the database with new tables as you update the software. In that case, you wouldn't worry about having your software add tables.

If you supply this software to customers or even remote sites within your organization and the installation and upgrading happens "in the field" then it's reasonable that each new version of your software should upgrade the database by itself. You don't necessarily want to do this checking before each SQL command and not necessarily on each run of the software. You can store a "data structure version number" somewhere in the database and check that number on startup, only applying structural updates if the number is below the current release.

Answer 9

Many RDBMSs support DDL SQL statements. ADOX can be used with some databases to do things like create new tables as well. I don't think this is particularly exotic at all, especially when a program is using some kind of "embedded" file based database as an internal datastore or even as an output format.

There is no problem creating indexes, constraints, relations, etc. as needed. Example:

Private Const WG_CONNSTRING As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" _
    & "Jet OLEDB:Create System Database=True;" _
    & "Data Source='$MDB$.mdw'"
Private Const DB_CONNSTRING As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" _
    & "Jet OLEDB:System Database='$MDB$.mdw';" _
    & "Data Source='$MDB$.mdb'"

'Exits with new MDB created, populated from initial data
'in text files, and cnDB left open.

Dim catDB As Object 'Don't early-bind ADOX objects.

Set catDB = CreateObject("ADOX.Catalog")
catDB.Create Replace$(WG_CONNSTRING, "$MDB$", MDB_NAME)
catDB.Create Replace$(DB_CONNSTRING, "$MDB$", MDB_NAME)

Set cnDB = catDB.ActiveConnection
With cnDB
    .Execute "CREATE TABLE Fruits (" _
           & "FruitID IDENTITY NOT NULL CONSTRAINT PK_FruitID PRIMARY KEY," _
           & "Fruit TEXT(50) WITH COMPRESSION NOT NULL UNIQUE" _
           & ")", _
             , adCmdText
    .Execute "CREATE TABLE Pies (" _
           & "PieID IDENTITY NOT NULL CONSTRAINT PK_PieID PRIMARY KEY," _
           & "Pie TEXT(50) WITH COMPRESSION NOT NULL," _
           & "FruitID INTEGER NOT NULL CONSTRAINT FK_FruitID " _
           & "REFERENCES Fruits (FruitID)" _
           & ")", _
             , adCmdText
    .Execute "CREATE VIEW PiesView (ID, Pie, Fruit) AS " _
           & "SELECT PieID AS ID, Pie, Fruit " _
           & "FROM Pies LEFT OUTER JOIN Fruits " _
           & "ON Pies.FruitID = Fruits.FruitID", _
             , adCmdText
    .Execute "CREATE PROC InsertPie(NewPie TEXT(50), FruitName TEXT(50)) AS " _
           & "INSERT INTO Pies (Pie, FruitId) " _
           & "SELECT NewPie, Fruits.FruitId FROM Fruits " _
           & "WHERE Fruit = FruitName", _
             , adCmdText
End With