I have a two forms and each form has its own table.
One form is called “Customers” and the other is “Invoices”. Both forms are linked to each other.
In the “Invoices” form, there is some information that is the same as “customers” (such as Company, name, address…ect.).
What I want to do, is once I click a button in “customers” to open “invoices”, but, also at the same time to fill out some specific fields by itself. Now I know that would be easy if they both were going into the same table, but, I want all information to be in two different tables.
How can this be done?
I hope I didn’t make my question more complicated than it should be…
I am not going to be able to do justice to relational database and normalization theories, but I will try to ramble through some brief explanations.
Tables are for storing data, forms for entering and viewing data, while queries are used (among other things) for retrieving data from one or MORE tables.
The proper setup of tables will allow maximum flecibility, maximize the speed of data retrieval, minimize errors, increase the ease of data entry and maintenance, and maximize your ability to retrieve data in its most meaningful form. Improper table structure will eventually hinder your application development (since ultimately everything else is built upon the tables) and once done will present a huge undertaking to correct. The extra couple of hours to initially get tables designed correctly may save weeks of future work. Always assume that an application will grow and become more demanding over time.
Data should be non-repetitive. Data should be entered in its most atomic form.
1234 East Main Street Chicago ILL 49186
should ideally be split between 6 fields rather than one.
Tables should be set up to hold related data. Whenever there is a one-to-many relationship between data, this is an indication that data should be split between 2 tables. A many-to-many relationship usually calls for three tables.
it is the use of primary and foreign keys that maintains the relationship between tables and the data within.
Duplicate data adds unnecessary bulk. It may restrict your ability to retrieve proper recordsets. And in the event a data entry error was made, you have a problem of locating all the instances of that data to make the corrections. You also end up with things like:
1234 West Main St., Chicago, ILL
1234 W. Main, Chicago, ILL
11234 West Main St., Chicago, ILL
Are these errors? or are they 3 correct and different addresses? Once this sort of data entry corruption occurs, it is almost impossible to clean up.
So back to your situation. One customer - many invoices (at least potentially). So two tables (at least). And it then follows that you will need two forms to present the data. Actually yours is a standard situation that every programmer runs into at some point. And you are probably going to need many tables (get used to the idea).
Table 1) Customer names (believe it or not, customers come with multiple names)
2) Customer addresses (mailing addresses, physical addresses, PO Boxes)
3) Phone numbers
4) Customer contacts (over time you will develop numerous contacts)
5) Invoices (Dates, shipping info, payment terms, etc.
6) Invoice line items (One invoice may have numerous parts, items, etc.)
7) Customer contact notes
This just touches on the basics and I will leave you to scratch your head.
The number of tables really does not matter, just that the right data is in them. I have a table that contains fields that lookup to another table with data that doesn't change. For example, a table with state abbreviations. The state table would contain the constants and then my address table would have a lookup field called 'state' that got the value from the state table. This actually makes things easier for the user.