Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am implementing an Oracle System to store event alerts from an application so I guess all the schema, database design will be handled by the application through a sript.

I am new to Database design, what are the top 5 design decisions which need to be made when implementing a database?

share|improve this question

migrated from serverfault.com Jun 28 '11 at 11:53

This question came from our site for system and network administrators.

7  
You're new to databases and starting out with Oracle? Good luck with that... –  growse Jun 28 '11 at 11:09
4  
@Growse: from an end-user point of view Oracle is a much better choice than say MySQL or SQL Server - although I would prefer to start with PostgreSQL. Oracle might be harder to administer than e.g. MySQL but it does offer a lot more features. And is closer to the SQL standard than MySQL or SQL Server - think of the string concatenation operator or the quoting mess in MySQL –  a_horse_with_no_name Jul 1 '11 at 11:34

3 Answers 3

General database design and database design on a particular platform are very similar but there are important and distinct differences. When designing a database you need to familiarize yourself with the concepts of normalization and de-normalization. A normalized database (typically 3rd Normal Form) are preferable for databases that will be handling transactional type applications. These are referred to as OLTP, or Online Transaction Processing, systems. I'm a SQL Server guy myself so I have practices that are specific and preferable to my platform. You may want to go check out http://www.infoadvisors.com as they specialize in data modeling. They are good resource for all things data modeling, which is something else you'll need to study up on.

If you use Twitter, I suggest following Karen Lopez (@datachick) as she works for InfoAdvisors and is one of the leading experts in data modeling. Also make use of the #sqlhelp hashtag if you have quick questions you need answered.

share|improve this answer

As @SQLChicken states, a good data model is a very important start to have for stable, consistent, extensible and scalable databases.

After you have a logical model, the next step is implementation of the logical model in the target RDBMS. There are many important considerations and actions that are required to do this. For example, depending on the anticipated size of your database, you may decide that certain structures should be de-normalized for performance reasons but this decision should be made carefully, with the implications fully considered. Other considerations:

  • If you haven't done so already, decide on a naming standard/convention. Name your objects in a consistent way. Take time to define each object such as tables and columns in the database by adding a comment to each.
  • Define your table primary keys, foreign keys, data constraints, and indexes. Indexes can be added later, so don't worry about getting them all up front. The need for them tends to become apparent over time ;-)
  • Define views of data that may be useful for reports, ad-hoc queries, etc.
  • Minimize trigger use - these can be expedient, but have many pitfalls.

By no means is this list exhaustive. Become familiar with the (voluminous) Oracle documentation.

share|improve this answer

Have a solid development methodology and toolset: an iterative approach; test framework; source control and builds; continuous integration; development standards; templates... and of course a team that understands how to use these things. Designing and implementing the development methodology are the first steps to designing a successful database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.