table structure for multi-stage applications

[daneharnett]

HI,
Im building a database in access and basically the user will gather information from a client in various stages...
stage 1 will be a small amount of data, then if the client is approved for the next stage more data will be gathered...
There is three stages to this process...
Im wondering what db structure i have... should i have 1 table for each stage? and link them... or 1 table with all data and just fill in the parts when acquired...

Also clients can make multiple applications so the data specific to the client that will not change over thier applications will be stored in a 'Clients' table...

Im happy to further explain this if required...

Thanks in advance

 

[lespaul]

You should design your database independent of information gathering and user interface. Figure out all the information that you will be storing in the database then design the tables based on normalization rules. Once the database schema is finalized, then move onto information gathering.

A quick example: you have a dog training school. You want to create a database that stores all the information about the dogs that attend and the tricks they can do. So you want to store the following information:

Dog Name
Dog Breed
Dog DOB
Classes Attended
Tricks Learned

Since a dog can attend many classes and know many tricks, this is an indicator that you will have one to many relationships between dogs and tricks and dogs and classes. In this example the table structure would be as follows:

Dog Table:
Dog ID (always have an ID field)
Dog Name
Dog Breed
Dob DOB

These are the only things in the dog table because they all deal with the dog itself.

Tricks Table:
Trick ID
Trick Name

Class Table:
Class ID
Class Date
Class Topic

Now you need intermediary tables to show dog tricks and dog classes:

Dog Tricks:
Dog ID
Trick ID

Dog Classes
Dog ID
Class ID

Now each dog can have many classes and many tricks. A sure sign that a table is not normalized is when the table structure looks like:

Dog Name
Dog Breed
Dog DOB
DogTrick1
DogTrick2
DogTrick3
DogTrick4


if you have multiple columns with the same name, it's a sure sign!

So at this point don't worry about the stages of information collection. Design the table structure in a normalized manner and then you can figure out how to fill in the tables. Trust me you will be happier in the long run having a normalized database.

HTH



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!