[thelist] Re: Database schema

[Ken Schaefer]

Hi,

Relational databases (and the language used to query them - SQL) have been
around for decades, and have been used to run hugely complex applications.
There are specific rules, and general guidelines that you need to follow in
designing a database, so that it's actually queryable using SQL.

Now, there is a tool called "Entity Relationship (ER)" modelling. Here you
have Entities (a bit like "classes" in the OO world), relationships (between
the entities), and attributes (a bit like properties). Normally, your
entities will become your DB tables, and your attributes will become your
fields. Relationships are constraints between your tables.

There are other modelling tools as well. If you're having problems with
database design (especially if you can model something in multiple ways in
an ER model), then a DFD (Data Flow Diagram) is useful. This allows you to
see the various actors in your system, and the actions that they would
perform, and the data needed to perform the action. That gives you an idea
of the queries that are going to be needed in the system, and you can pick
the most appropriate schema to match.

This is the book that I usually recommend to people:
http://www.amazon.com/exec/obidos/tg/detail/-/0201708574
Database Systems: A practical approach to design, implementation and
management.

Besides some indepth discussion of transactions and so forth, it also covers
ER, and EER (Enhanced Entity-Relationship Modelling), normalisation and so
forth. You would probably need a good Systems Analysis book to cover DFDs in
depth.

However, it appears that you are in a bit more of a hurry, so some of the
web based links that others have posted may be of more use to you.

That said - if you want to build any sort of workable system that's going to
be maintainable into the future, you *must* understand these modelling
techniques. Otherwise you start to run into the issues that you have "the
table is becoming unmaintainable". The techniques for building databases are
well known - and no database should be difficult to maintain and query. You
just need to know how to design/model it.

I can't stress this enough - you simply can not build a moderately complex
database backed system without knowing some theory. Spending 1-2 days
grabbing as much info as you can on the topics will be a very worthwhile
investment. It'll save you heaps of time down the track.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Thanks for the tip. So basically from all the advice that I've
: gotten so far what is recommended is putting product specific
: details in separate tables. Could anyone show me what a query
: for displaying all products in the hard-drives category would look like
: in a case like this? Learning by example is the best way? A
: rough draft of a query would do.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

...and what are you going to do when you have 500 classes of product? Have
500 tables?!? That sounds a little crazy to me. But maybe you're not
intending to have 500 classes of product? We don't know, because you don't
know (or you won't tell us!)

Also, sticking with the topic of "hard drives" - that seems fine for a stock
standard internal IDE drive. You could extend it relatively easily to
various sizes (3.5", 2.5", 5.4").

What happens when you have SCSI drives? They have a bunch of extra
properties not applicable to IDE drives: Ultra320 -vs- Ultra160? LVD -vs-
SCA? 68pin -vs 80 pin?

And where do you put the external drives? (that have USB -vs- Firewire
connectors), and NAS devices? and PC-Card hard disks? and CF Card hard disks
(like the IBM microdrives).

One possibility is the table layout that I specified before where the
properties and their values are stored as rows, and you use some kind of
roll-up query to get these into columns.

But what you choose really depends on what your system needs to do!

Apologies for making this so hard for you, but you're really putting the
cart before the horse. You need to know what the system has to be able to do
before you can design the table.

To give an analogy (albeit a bad one), you're asking us whether you should
put the website navigation on the left, or the top of the page, without
giving us the slightest idea what the website's about, or how complex the
site is, how big it is, and how "deep" it is (it is very deep/nested, or
very shallow).

Cheers
Ken

Microsoft MVP - Windows Server (IIS)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Cosmin G" <cosming at as.ro>
Subject: [thelist] Re: Database schema


: Well, too many answers, but none too clear. Maybe I should have specified
: that I'm using MySQL. Which doesn't support many of the features that you
: mentioned such as foreign keys, one-to-many relationships (searching the
: manual for one-to-many found 0 results).
: >Seriously, you need to use the tools that are out there that have been
: >developed to solve exactly these types of problems ("what database schema
: should I use?")
: Which ones would those be?
: When telling someone something like
: >
: > However, as stated before, you're jumping the gun. You need to do some
ER
: > model *first*. You need to create some DFD (or similar) *first*. Once
you
: > have these things, the database schema will write itself.
: >
:
: You're not being helpful, especially to someone who's not very proficient.
: What's an ER and what should it contain?
: And what's wrong with posting my .sql file? It's not like I've posted a
: virus, right?
:
: >I think it would make sense to break up the products table a bit.  Any
: >time when you find yourself with a table where you would have a large
: >amount of fields that are not getting used, it's a good candidate for
: >breaking into more specificized tables that get joined to the original.
: >This is a clue that the table is decribing multiple entity types, which
: >is usually suboptimal.  I would suggest putting the printer and scanner
: >specific fields in a common table though to accomidate
: >printer/scaner/fax devices.
:
: Thanks for the tip. So basically from all the advice that I've gotten so
far
: what is recommended is putting product specific details in separate
tables.
: Could anyone show me what a query for displaying all products in the
: hard-drives category would look like
: in a case like this? Learning by example is the best way? A rough draft of
a
: query would do.