Good Database Design

[siphilp]

I am writing a paper on good database design and would like your input.
Do you follow any guidelines set by
a) your company
b) your database provider
c) personal
d) your boss
e) a book?

If so would you like to share these guidelines. Thought i would post this as it might also help any newbies looking for somewhere to start within the world of database design

Thanks for you input


Si

[DevilBear]

I follow what I thought was a pretty standard process... lay out the "screens" that will be needed, so you know what data the database will need to hold, determine if any additional data needs to be included, then design tables around "nouns" (e.g., "customers", "links", "contacts") and take the tables through the normalization steps.

[MarcusJT]

Normalization is one of the most important concepts, as is proper use of stored procedures / parameterized queries.

If you're talking about using Access as a frontend for forms, reports, etc then naturally even more rules apply, but that's not my bag, baby!


M@rco

[MarcusJT]

Aha! I knew there was a good link somewhere:

http://www.4guysfromrolla.com/webtech/030399-1.shtml
(see also the references at the end of each section)


M@rco

[MarcusJT]

And this one:
http://www.webmasterbase.com/article/378


M@rco

[Hierophant]

As a database programmer you make the guidelines. You find out what information needs to be stored and then design the database to fit that needs.

Chances are your boss hasn't worked with a database in a few years and the changes between versions can be remarkable let alone a few years.

Your database provider can give you ideas and guidelines to help design a good database. Same with books but they need to be up to date so you know how new datatypes are handled.

Never take programming advice from your company at large. They are not programmers and won't know anything about optimization and data storage. Listen to their ideas, say that you'll look into it, then design an application that works and gives the information they need. If they see it on the screen they could care less how it is stored.

Personally, what I do is take all my bits of information and sort it into groups... i.e. User, Article, Comments, etc... From there I develop my fields. There are two ways to develop your fields and other programmers can help me out here.

The first is to make sure each field takes up the least amount of space. Unfortunately, contrary to popular belief this can actually waste memory because of file system blocks and paging mechanisms. However on small systems you can get something running really quick using this method and optimize it later.

The second method is look at your records in relation to you page sizes. If you can use a complete page with XX number of records or even 1 record you will make your application a little faster and more robust, use memory better but at the expense of file storage size.

Both have their place depending on the system used and requirements of the project.

If you need a book to learn the basics of SQL DDL programming check out:
http://www.amazon.com/exec/obidos/AS...627107-9001717
or
http://www.amazon.com/exec/obidos/AS...627107-9001717

My suggestion is to work with two different systems... You can get MySQL for free, Access is cheap and there are developer licenses for SQL Server and other systems that are affordable. If you use Linux/Unix you can add Postgre SQL as a free solution.