Database Structure help

[Guest]

Hi,

I am new to designing databases and I'm currrently trying to figure out the
best way for me structure a database that will store all the information
relating to our company files.

The main problem that I am having is that each file is designated a
"Category", for example Intellectual Property is category "1001-2000" and all
files that relate to this category must have a file number that falls between
these numbers. This structure was set up before my arrival, and the numbers
are used to quickly identify by the number alone, what type of file it is.

So, I have set up individual tables with the same structure but have
assigned them with different autonumbers starting at the first number of the
category.

Below is an example of what I have for these types of tables.

Here is what I have so far:
Table1
Company Name
File Name
File Category#
File#
Date Opened
In File
Progress Stage (drop down box)
Follow up (check box)
Follow up Date
Status
Signed Documents (hyperlink)
Date Closed
File Box#


I am also wanting to add in information about each company we deal with.

Table2
Company Name
Contact Name
Email
Address
Phone#
Fax#
Sales Associate

Can I combine all these category tables into one table, and then create a
form to enter in all the information?

Any suggestions would be great.

 

[Guest]

So, I have set up individual tables with the same structure but have
assigned them with different autonumbers starting at the first number of the
category.You should have one table for all categories. Do not use an autonumber in
the category field but use a text field as you are not doing math on it.

I assume your File# field is to record all files related to a File Category#
and if this is true then it should be a text field. These two fields would
then be use to make a compound index that does not allow duplicates.

Why company name in the file table? You know who you are.

 

[Guest]

Hi Karl,

So, yes my Category field is actually text, but I need the File# field to
automatically fill in the numbers, so I don't think I can turn this into a
text field. By splitting out the categories into different tables, I can
automatically generate numbers starting with the category number. When I
create a new record I would like the number to automatically generate. For
example:

Category File Number
1001-2000 1001
1001-2000 1002

But maybe I'm going about this all the wrong way....

Also, in regards to the "Company Name" this is not for our company but for
the various companies that we deal with.

 

[Ron2006]

Auto gen numbers may skip numbers - will start over with a higher
number if you have to recreate a table - may skip numbers. If you
import a number or records and then decide that they were wrong, you
will NOT be able to re-import them to be the same set of numbers if you
use the autogen numbers.

It is NOT a good idea to try to use them as an integral part of any
number you show to the user. (for instance if the users decide that for
some reason all file numbers should now start with 3, you will NOT be
able to do that with the autogen number.)

There are other ways of creating sequentially numbered fields that you
want to have control over.

If the company name is in table 1 it should not be repeated in table 2.
you will already know the name (from table 1) for every record in table
2 that is assocaited with that particular table 1 record. If Company
ABC changes their name to Company XYC, if you keep the name in two
places you will have to write something to change the name on all the
records. In fact Company name as such should not be in table 1 but in a
company table, with the key (autonumber) of the company name record
being what is stored in table 1. If you don't do this then you will
have the same problem if the company name changes.