Database Design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Love_Daddy
Forum Commoner
Posts: 61
Joined: Wed Jul 10, 2002 6:55 am
Location: South Africa
Contact:

Database Design

Post by Love_Daddy »

Hi Guys,

What's a good way to start a database design.
I need to do a conceptual design of a Virtual Office project I'm working on.
Any ideas?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Start by listing the "things" that are involved in the project. Brainstorm about them for a while and make a big list. Its much easier to end up not using something that having to fit it in later. These "things" typically will become your tables.

Next start defining the "things", what information do you need to describe each thing. These will become your table columns.

Then start describing the relationship between things. Is it 1:1, 1:m, m:1, or m:m. If its 1:1 does it makes sense to combine the things? m:m's will create seperation relationship tables relating the foreign keys.

Typically as you're working on the relationships phase you discover that you left out some "thing" that makes the relationships more intuitive, so you'll go back and iterate over the steps a few times.

Finally, review the normalization rules and apply them to the schema you've developed. For most database application, 3rd Normal Form is a good target. You'll probably not reach it for all your tables, at times there are good reasons to break it. But its always good to understand why you broke it as opposed to not doing it in the first place. (I happen to like 4th and 5th Normal Forms, but then again my applications will likely never have the extreme load that argues against these.)
User avatar
phpPete
Forum Commoner
Posts: 97
Joined: Sun Aug 18, 2002 4:40 pm
Location: New Jersey

Post by phpPete »

Nielsene, that's a great way of putting it all in laymens terms!

One thing I like to do is determine the kind of reports/output needed, it helps me to get a handle on the relationships etc...

I've used this site a lot, and it's quite good for beginners:
http://www.sqlcourse.com/

cheers

Pete
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Yeah I use the reports/output needed to double check my design after I've finished the first pass and then go back to fix what I left out.

Then I try to come up with at least 4 "cool" featues a user/client is likely to request that weren't initially asked for and see if the design accomodates those, repeat adding/adjusting the tables to fit as appropriate. If one of these cool features really complicates the design I might leave it out, or talk with my users/client to decide if the added complexity/overheard is worth the delay in other functionlity/ increased cost/ future expandability, etc.

Then again I've been working on projects where I'm lead developer and DBA, but sometimes with another few developers working, so changing a schema later could break lots of people's work not just mine. (I'm willing to create work for myself, not for others, especially in cases where the co-developers are volunteers)
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

Here is some good explanation of the ER model, Conceptual Design and the Relational model if you really want to get down to the nitty-gritty of it.
Ya gotta love relational algebra. <g>
http://www.cs.wisc.edu/~dbbook/openAcce ... es3ed.html


Direwolf
Post Reply