Problem solve Get help with specific problems with your technologies, process and projects.

Table design: go across or go down?

Table design: "go across" or "go down"? Ok, what do I mean? Consider a monthly expense DB. One table design (go across) could have a different field to denote different types of expenses. A query on this table would require you to select each column that you wish to sum. Also, on a form, you could enter in each expense for that month. This (go across) design means that one record denotes one month's expenses.

Another table design (go down) would have three columns: month, expense type and value. Many records would denote one month's expenses. A query is easily constructed to calculate expenses for each month. Now, the down side: on a form (default design) you will only see one expense for each record, not one month's of expenses. Ok, how to show one month's expenses?

I'm told that one can write VBA code to enable a form to show multiple records for one record (multiple expenses for one month's expense). Here's what I know: some VBA code (executing queries in Access and exporting to Excel use recordsets, query def, things of that sort). Also, I think I could use a sub form, but will this allow for data entry via the form? Too, if the code actually does a query, what are the speed implications?

Can someone send me examples or show me where to look? Too, how do Microsoft Money and Quicken design their tables? Thanks.

Whew! That's a monster question, but I'll tackle it!

Always, always, always use the normalized form, which you call the "go down" design. This is far more flexible and less work in the long run. Ask anyone who has had to maintain both forms of a table... All of the professionals will choose the normalized form, every time.

A simple view can be used to convert the normalized form of the table into your "across" form. This makes the display on a form easy, although the UPDATE can be a little tricky if you aren't careful. There are many ways to handle the UPDATE in Access, but the choices vary depending on which version you are using and what design you've chosen.

Both Microsoft Money and Quicken use normalized data structures.

This question is continued.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Database Design Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your database design -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.