Form Design

[Wet_Blanket]

I have a form that will be used by multiple user in order to input new account information. Depending on what is selected, the data can be stored in 3 different tables, and once this code is executed the data is "erased" from the form. I also took away the navigation buttons. The reason for this is that I didn't want users to be able to scroll backwards through the data and eventually there would be thousands of records.

Problem though is implementing an audit trail (like Irish's) because there aren't any real "records."

Is there a better way to do this?

 

[HiTechCoach]

IMHO, you are making it a lot more difficult that you need.

Just to make sure, since this is multi user, that you you split your database and each user have their own copy of the front end that is not shared. Is this correct?

 

[Wet_Blanket]

You are probably right about it being more difficult than needed. But yes, my plan is to split the database - I just haven't yet because I have seen it is better to do this after you have the database designed.

 

[Wet_Blanket]

Okay, let me pose it with the first obstacle.

I am designing a database to be used to record account information. Accounts can be on one of three programs, and each program will have its own unique fields (or common fields). Ex. Program 1 has 9 fields, Program 2 has 5 fields, etc.

Because of this, I decided to have a seperate table for each program so that there wouldn't be one table that contained null fields. However, I want the user to be able to input the data using one form. Depending on what program is selected on the form, the data is stored in the appropriate table.

Is this a good way to do this?

 

[HiTechCoach]

Is this a good way to do this?

I would have to say definitely NO. Since you want to use one data entry for for all three different program types, you really want a single table.

I would urge you to follow the rules of data normalization. I would avoid letting your data entry needs influence your table design to the point it violates the rules of normalization..

As a general guide line, every table used for data entry will need a separate data entry forms. This is true even for relates tables. You would use a parent form and use a sub form control for the forms for the related tables.

So if you have three program tables, you probably should se three separate data entry forms.

From years of experience:
If the same form can be used to enter data into multiple tables, then that usually is a red flag of a normalization issue and that the table should be combined into a single table.

 

[Wet_Blanket]

Is it better to have one table that will have null values for some fields depending on what program it is? If it were one table, there will be atleast 20,000 records. Is this a non-issue?

 

[HiTechCoach]

Is it better to have one table that will have null values for some fields depending on what program it is?

It is OK to have some fields not user for every record.

It may mean that you will want to use a related/child/sub table that has a record for each unique attribute. I like this method because ti allows new attributes to be added without any design changes of tables, queries, forms, reports, etc.

Sounds like you might want to use the "attributes" in a separate table method.

If it were one table, there will be atleast 20,000 records. Is this a non-issue?

20,000 record should not be a problem at all.

I have database with tables that have 250,000+ records without any issues.

Note: I split all my database from the start. I also design and test with the back end on another machine from the start.I also load test very early in development by adding 100,000 + records in all the transaction tables.

TIP: by splitting my DB, I can have multiple test back ends.

TIP 2: because I started split, I can have test front ends to experiment with different methods and compare performance. Also it allows to test multiple users hitting the system from the install design. I want to handle this as soon as possible. Not have to rewrite stuff latter when you find out performance is poor because you did not test properly.

IMHO, testing is the most import part for deploying stable and function software. I also believe it its the he most overlook and skipped part by way to many people. By splitting your database from the start, it allows for better testing!

 

[Wet_Blanket]

That all makes sense - thank you for the advice.

 

[HiTechCoach]

You're welcome.

Glad we could assist.