tables with shared information

[Carrie]

I am currently creating a database that a variety of users
will be using to fill out forms which they will then
print. Each form is for totally different information
(but they are all related) and they all contain a lot of
information.

I thought the best thing to do would be to create a table
for each form that needs to be completed (to keep the
number of fields down (there are probably a minimum of 45
fields per table) but, each form will include some
information that is the same (Req #, License #,
Coordinator...). Once they have filled out one form (ie.
Form A) they also need to fill out form B, C, D, E for the
same Req #. I would like all the information that will be
duplicated to automatically fill in to the remaining forms
so that they do not have to retype the info. I have been
working on my tables and cannot seem to get this to happen
(ie. I type info into Table A and even though it has a
relationship with Table B, Table B does not have the
relevant info). From reading some different threads in
Newsgroups, I think that maybe the forms will link the
information once created but, I want to make sure this
gets updated in the table as well. Am I totally on the
wrong track?

 

[Gina Whipp]

They say the hardest part of designing a database is the planning and they
could be right...

If it were me here's how I would set up the tables:
Table 1: Information that only needs one entry, ie, Req #, License #,
Coordinator. etc... AND let's not forget that all important Primary Key
Table 2 thru ???: Information for the forms not included in Table 1 but
linked, thru Realtionships, to Table 1. You should have a table for each
form so you don't end up with a zillion fields in any Table.

Once you create the tables you can then move on to the forms. Note,
anything typed in the form once tied to the table is automatically written
to the table. A user can click to go to a new form, hence going to a new
record in the table.

Hope that helps...
Gina

 

[Tim Ferguson]

I thought the best thing to do would be to create a table
for each form that needs to be completed

No, sorry. Like Gina says, there is much more to getting the design right
than this, and if you get it wrong you are storing a great deal of work for
yourself further down the road.

The first rule is One Table Is One Thing -- a thing (entity) can be a
physical thing like a Car or a House or a Wharehouse, or it can be a more
abstract thing, like Payment or Registration and so on. When you think you
have all your entities separated out, you write down all the descriptors
(attributes) for each entity, like Car.Colour, Car.Maker, Car.IndexNumber;
House.Address, House.RoofType, House.Owner.

At this stage you usually identify a whole bunch of new entities: Owner,
Manufacturer and so on. Go back to rule One. Repeat. Repeat.

When you have got all these ready, and the way they relate to each other on
the way, you should have used up about 90% of your development time. And
you have not even switched the computer on yet! Seriously, the actual
coding of tables and forms is the very last little bit of getting the
design done, but when you have done that, still be ready to spot the errors
and .... yes, go back to Rule One again.

In most paper-driven environments, forms give you good insight into the
tasks that people do, and your final application must be able to support
all those jobs. It is probably quite unlikely that the screen will look
anything like what the paper ones do, and that is a good thing --
otherwise, why use a computer at all? But the data modelling, tables and
queries, must be dictated by what you actually know about the information
being handled.

Systems analysis is hard, but it's much better fun than coding!

All the best


Tim F