Page 1 of 1
Database Design
Posted: Mon Sep 16, 2002 3:34 am
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?
Posted: Mon Sep 16, 2002 8:18 am
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.)
Posted: Wed Sep 18, 2002 9:55 pm
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
Posted: Wed Sep 18, 2002 10:22 pm
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)
Posted: Thu Sep 19, 2002 6:18 am
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