Database Design

Question

I need to create a database to store simulation results. What resources are out there to help me design this?

EDIT: For those who want more details...

We work with a discrete event simulation used by the military. Important simulation events are thus when weapons are fired, when kills occur, and also when weapons fail to kill a target. Other important events are when targets are detected or when they are no longer tracked. We keep pretty detailed log files that we then mine to calculate averages and other statistics. It's mainly the statistics that I'm concerned with storing in the database.

Another thing I want to include is the case ID. We identify different cases using an alphanumeric case ID string that corresponds to a particular combination of systems and important input parameters. For example, suppose a sensor could detect a target at 3 different ranges depending on mode, there would be a character in the case ID that corresponds to each level of detection range, labeled 0, 1, or 2. If there are enough levels of a given case, the case ID characters switch to letters after 9. This case ID would definitely be an important key in the database. Once we have stored the data, we need to retrieve certain numbers to make charts comparing different cases. We currently make the charts in Excel, but I've been adding more MATLAB recently.

As for the database technology, I thought I'd do the table design in Access to prototype things, then implement it in MS SQL Server. I'm planning to use Ruby to create HTML reports with data tables and graphs.

Answer 1

There's a website call "Stack Overflow" that has a bunch of smart people who can help. ;-)

Provide some more detail on what you're trying to store, and I'm sure you'll get a bunch of helpful answers.

Answer 2

Provide the following and you'll get a helpful answer

1. Database type (MsSQL, MySQL etc)
2. Any other languages you are using with this (C#, PHP)
3. The data you want to store
4. What you plan to do with the data (it may affect how best to store and query it), this step is very optional

Answer 3

One thing that can help is having an entity relationship diagram. This will help you design the different tables that you wish to use and how these are interrelated. This may be overkill for your needs if your simulation does not need to use more than a couple of tables.

A cheap solution (not free) we've used is Happy Fish. It provides a good way to visualize the data even for a somewhat complex application with over a hundred tables. This tool will automatically generate the schema DDL to create the necessary tables, indices, foreign key constraints for your environment (support is included for Oracle, MySQL, Microsoft SQL Server, and others).

There are many additional tools in this vein; you can find a selection of free tools from Wikipedia.

Answer 4

If you are using a relational database (hint: probably yes) then the first thing to know is how to normalize your data (i.e. structure your tables properly).

Good starting point: this Wikipedia article.

Answer 5

Simulations of what?

The book Data Model Patterns: Conventions of Thought by David C. Hay has design recipes for a number of business types. Maybe you can be inspired by a model from there?

Answer 6

Often, we write down the use cases -- who's going to use it and what they're going to do.

Sometimes, we write down the things that will be in the database. The entities, their attributes, and the different things the entities do.

Once we have some basic background, we often draw diagrams of the entities and how they relate to each other.

Given all of this, we try to find folks with some experience who can help us map our pictures onto available technologies and make some recommendations for next steps.

Answer 7

I would suggest that you prototype in the database you plan to use. Access is not appropriate for this type of data over lthe long term. Since Access Sql is very differnet from SQL Server t-sql and access dataypes are differnent from SQL Server datatypes (and the wizard makes very poor choices when converting), using Access to prototype will not gain you anything and may create more work recreating things you already had worked out.

Answer 8

As far as a DBMS foundation: Use SQL Server Express, then move to SQL Server if you need to. Do not use Access for this.

Answer 9

If you're going to use Ruby, then consider building your database using ActiveRecord (a core component of Rails, but is not exclusively tied to it). That way you get to evolve your schema using migrations, which is a nice way to manage things. By default your database gets implemented in SQLite, which if you don't mind losing the graphical aspects of Access, is a nice little package (and probably the most-implemented DB software in the world, which not a lot of people know). Moving up to SQL Server should be fairly pain-free (I've done it - no major scars).

If you go that route, and plan to output HTML via Ruby from your data, why not go the whole hog and build the whole thing as a Rails-based web site? The number of wheels you avoid reinventing might be worth it.

(Disclaimer: I'm a born-again Rails programmer with a long history of Windows-based DB application development. Apologies for any inadvertent evangelism.)