The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

20% simplicity in database design

So you have an idea. You read Joels article. Makes sense. Implement the 20%. But in the knowledge that sometime you will want the other 80%.

It's a database backed thing. What do you do about the database?

1. Do a full design first, knowing that intitially you won't implement all of it?

2. Do the tables you need right now, but design to rigorous 5NF, because that makes any future changes possible/easier?
20% guy
Sunday, December 10, 2006
 
 
Regardless of which approach you take your first cut of the database design will require changes. My advice is to do what you need right now, but do so within a framework that allows for easy incremental schema updates as you move forward.
Andrew Lighten Send private email
Sunday, December 10, 2006
 
 
"...but do so within a framework that allows for easy incremental schema updates as you move forward"

Thanks. And generally speaking, is a db schema that is designed according to relatively 'strict' and 'correct' relational principles one that is easy to do that with?
20% guy
Sunday, December 10, 2006
 
 
ALTER TABLE is your friend.
dbGuy
Sunday, December 10, 2006
 
 
In general you go about designing a database as you go about designing the rest of your application. With your best current knowledge, design for reasonable extension and attempt to encapsulate what varies.

Just as in normal software design, this is hard and you'll probably not get it right the first time (especially the reasonable part).

For example: You hava a user and she has a phone number. Do you put her number in a seperate table? If your roadmap, todo list or plan says that you'll want to get multiple phone numbers of people after the next production release, then the answer is yes.

If you're concerned about integrity, the slap a unique constraint on the foreign key for the time being.
Peter Monsson Send private email
Sunday, December 10, 2006
 
 
A few months back I blogged about the way my app does this. See http://blog.actapps.com.au/2006-06-03/automatic-database-upgrade/ if you're interested.
Andrew Lighten Send private email
Sunday, December 10, 2006
 
 
To quote Fred Brooks: "Plan to throw one away." You do your first design with the express intention of trashing the entire thing and starting from scratch, hopefully having learned what sorts of things NOT to do and having some ideas of how to do those thing better.

There is no need to complete this first shot. Generally you will come to a point with the first attempt where it is obvious that you cannot make further forward progress with the existing design, where the accumulated bad design and kludged fixes simply overcome everything else. This is when you throw it away and start over. Some small parts of the first attempt MAY be savagible, but don't count on it.

The only alternatives to "plan to throw one away" are either slog on with the terrible mess that you got yourself into due to lack of planning, or spend a huge amount of effort on planning and design before you have a firm grasp of the problem and possible solutions. Planning to throw one away is MUCH more economical.
Jeff Dutky Send private email
Sunday, December 10, 2006
 
 
"And generally speaking, is a db schema that is designed according to relatively 'strict' and 'correct' relational principles one that is easy to do that with?"

Yes, in general. When people come up with denormalized designs, those designs are usually based on the needs of the current application instead of the nature of the data. That said, full normalization is only helpful up to a point. There is a point beyond which the flexibility gained is not worth the added up-front development cost. Being able to find that point comes from experience.

To answer your question, I first try to think about what new features will most likely be needed in the future, and what database structure will be needed to support them. When I think it will be easy to change the database to support a feature down the line, I don't worry about it. When I think the change will be hard, I design the database to support that feature from the beginning.

Unfortunately, there is no silver bullet here. I sucked at this at first, but I've gotten better.
clcr
Monday, December 11, 2006
 
 
Changes in the database schema tend to be difficult to propagate through the business logic, especially when you're moving from a simplified (maybe denormalized design) to a more rigourous representation. I'd go for getting it right the first time -- you'll generally find that the application is easier to write anyway when your database is a logically correct representation of the business environment
David Aldridge Send private email
Monday, December 11, 2006
 
 
I'd like to advocate the middle ground.

Design both the database schema and the immediate code to access the database together at the same time. As you create tables, create the objects encapsulating the SQL queries.

I'm proposing this technique is because quite often you will break tables up because the normalization rules suggest it or the context of the data does, and then discover that you will be joining them together than you thought.

For example, we used to record outgoing notifications in one table and incoming responses in another table. A little desk checking quickly revealed that 95% of the time we did a select, we wanted to know which notifications had responses, which responses were unsolicited, and so forth. Creating it as one big table (and setting fields to null as appropriate) would have saved us some programming effort.
TheDavid
Monday, December 11, 2006
 
 
"I'm proposing this technique is because quite often you will break tables up because the normalization rules suggest it or the context of the data does, and then discover that you will be joining them together than you thought."

Don't neglect to explore RDBMS-specific physical features that can mitigate such issues. For example in Oracle two frequently joined tables (say invoice_header and invoice_line) can have their related rows co-located physically by placing them in a cluster on the common join key (eg. invoice_number, or customer_id and invoice_number even). This leads to very fast screen response time for the display of a customer's invoices and detail.
David Aldridge Send private email
Monday, December 11, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz