views:

355

answers:

5

I have been given the task to design a database to store a lot of information for our company. Because the task is rather big and contains multiple modules where users should be able to do stuff, I'm worried about designing a good data model for this. I just don't want to end up with a badly designed database.

I want to have some decent examples of database structures for contracts / billing / orders etc to combine those in one nice relational database. Are there any resources out there that can help me with some examples regarding this?

+3  A: 

The Data Model Resoruce Book.

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_cp_ob_b_title_0

HEAVY stuff, but very well through out. 3 volumes all in all...

Has a lot of very well through out generic structures - but they are NOT easy, as they cover everything ;) Always a good starting point, though.

TomTom
+2  A: 

Try to keep your concerns separate here. Users being able to update the database is more of an "application design" problem. If you get your database design right then it should be a case of developing a nice front end for it.

First thing to look at is Normalization. This is the process of eliminating any redundant data from your tables. This will help keep your database neat, and only store information that is relevant to your needs.

James
Thanks for your answer, i have been to school for this and know what normalization is. I'm just very careful here since i don't want my first like real project out there to fail :). That's why i'd like to see some example normalized relational db's.
Younes
@Younes - Don't worry too much about projects failing, it happens to everyone...even the best developers :) As long as you are thorough in collating what information *must* be stored in the database then designing the database shouldn't be too difficult.
James
@James thx for your comment, i'll try not to worry too much.
Younes
A: 

The database should not be the model. It is used to save informations between sessions of work.

You should not build your application upon a data model, but upon a good object oriented model that follows business logic.

Once your object model is done, then think about how you can save and load it, with all the database design that goes with it.

(but apparently your company just want you to design a database ? not an application ?)

Matthieu
Correct, but i already knew that. It's not that i don't know what a database is for or anything ;). Just want a decent db to work with.
Younes
Multiple applications might share a single database. Plus the integrity of a company's data can be crucial to its very survival. So it is important that the database accurately models the company's data.
APC
Worst method I can think of to design an enterprise-type database.
HLGEM
+2  A: 

Barry Williams has published a library of about six hundred data models for all sorts of applications. Almost certainly it will give you a "starter for ten" for all your subsystems. Access to this library is free so check it out.

It sounds like this is a big "enterprise-y" application your organisation wants, and you seem to be a bit of a beginner with databases. If at all possible you should start with a single sub-system - say, Orders - and get that working. Not just the database tables build but some skeleton front-end for it. Once that is good enough add another, related sub-system such as Billing. You don't want to end up with a sprawling monster.

Also make sure you have a decent data modelling tool. SQL Power Architect is nice enough for a free tool.

APC
hi there, I tried to download the free tool. googlecode.com gave an error about the file being too big and hence blocked. Can you share with us what you downloaded ?thank you, Anjan Bacchu
anjanb
+1  A: 

Before you start read up on normalization until you have no questions about it at all. If you only did this in school, you probably don't know enough about it to design yet.

Gather your requirements for each module carefully. You need to know:

Business rules (which are specific to applications and which must be enforced inthe database becasue they must be enforced on all records no matter the source),

Are there legal or regulatory concerns (HIPAA for instance or Sarbanes-Oxley requirements) security (does data need to be encrypted?)

What data do you need to store and why (is this data available anywhere else)

Which pieces of data will only have one row of data and which will need to have mulitple rows

How do you intend to enforce uniqueness of the the row in each table? Do you have a natural key or do you need a surrogate key (suggest a surrogate key in almost all cases)

Do you need replication?

Do you need auditing?

How is the data going to be entered into the database? Will it come from the application one record at a time (or even from multiple applications)or will some of it come from bulk inserts from an ETL tool or from another database.

Do you need to know who entered the record and when (highly likely this will be necessary in an enterprise system.

What kind of lookup tables will you need? Data entry is much more accurate when you can use lookup tables and restrict the users to the values.

What kind of data validation do you need?

Roughly how many records will the system have? You need to have an idea to know how big to create your test data.

How are you going to query the data. Will you be using stored procs or an ORM or dynamic queries?

Some very basic things to remember in your design. Choose the right data type for your data. Do not store dates or numbers you intend to do math on in string fields. Do store numbers that are not candidates for math (part numbers, zip codes, phone numbers, etc) as string data as you may need leading zeros. Do not store more than one piec of information in a field. So no comma-concatenated lists (these indicate the need for a related table) and while you are at it if you find yourself doing something like phone1, phone2, phone 3, stop right away and design a related table. Do use foriegn keys for data integrity purposes.

All the way through your design consider data integrity. Data that has no integrity is meaningless and useless. Do design for performance, this is critical in datbase design and is NOT premature optimization. Database do not refactor easily, so it is important to get the most critcal parts of the performance equation right the first time. In fact all databases need to be designed for data integrity, performance and security.

Do not be afraid to have multiple joins, properly indexed these will perform just fine. Do not try to put everything into an entity value type table. Use these as sparingly as possible. Try to learn to think in terms of handling sets of data, it will help your design. Databses are optimized to do things in sets.

There's more but this is enough to start digesting.

HLGEM