Do I need to split tables into smaller tbles?

[Switchwork]

Hello all I was reading a post just now and noticed that someone mentioned splitting table into smaller tables. My main table has 23 fields so I was wondering if I should maybe split it down into smaller tables is that supposed to be done?? And if so how do I do it?? Do I then have to split the form that I have based on that table? Could someone please let me know.

One more thing is I am wanting to design another table called Exports to enter all the jobs in it that are exports how should I go about doing this and what tables should it be related to. I have four of them:

Customer
Employees
Job Details
Vehicles.

This table will be specifically for one customer as there is more details to enter into a table which is why I want to have a seperate one for those jobs. If I relate the customer table with the new exports table the relationship field should be Customer ID should it not?? NOt sure about the primary key though. The JobDetails table primary key is JobNo and it is autonumber. I use this number as my reference number for any queries I get fromcustomer. I don't want the new table to have a different way of numbering but not sure how I should go about doing it. could I set the beginning autonumber for the start of the new table. ??

Anyway please help me with this and thanx in advance

 

[Uncle Gizmo]

Hello all I was reading a post just now and noticed that someone mentioned splitting table into smaller tables. My main table has 23 fields

23 fields sounds rather a lot four one table. What are all these fields for? If you post a list and a brief explanation for the ones that aren't obvious it will be easier to help you decide if you should spit this table into smaller tables.

one mistake that is quite often made is to include duplicate information in your table. For example, a table for storing invoice details may also include the invoice address, so for customer ordering several orders over a period of several months then the table could have the address information listed hundreds of times. A better way is to put the address in a separate table, the address would have a unique ID for example 50, so in your invoice table you just have one field for the address which would have the number 50 in its so you would know this invoice was for Address number 50.

 

[Navyguy]

Hello Switchwork

What you are looking for is information about "Normalization". My understanding of it is that "all similar information" is stored in the same table, meaning that a table may have 24 fields that are similar to each or may have 3 tables of 8 similar fields within each other.

Your example of employees could look like… a table for employee "Tombstone" information, SSN, DOB, address etc. All of the employees may have some qualification or training, so you may want to create 1 table for "Qualifications" and 1 for "Training". These would be linked by some field (presumably by an EmployeeIDNum) so therefore if the employee receives training it could be recorded in the TblTraining and if they recd a qualification it could be recorded in the TblQualification.

There are numerous examples on normalization here, so do a search. I have said this many times that I think this is key to limiting problems in the future when you need to expand. modify your DB as it grows etc.

Good Luck!!

 

[The_Doc_Man]

First question: How many fields are too many? Answer: It depends on your problem. I have a case that is reasonably normalized (at least, I think it is) that has over 40 fields, mostly small numbers or short strings, plus a couple of whompin' big text or memo fields. I have considered how many of those should be split. When I inherited the table from my predecessor, that big table had over 80 fields. I've split it to six tables so far. But note that I still have a bunch of fields.

Second question: Resetting autonumber in a separate table? Answer: You can do it but the real issue is whether you are asking too much of the autonumber. It makes sense to say the autonumber is also some sort of ticket number. But in truth, the more meaning you give to an autonumber that you are using for links, the worse off you are. If you have to ask about resetting an autonumber on a separate table, you are asking for trouble 'cause your design should not care.

Third question: Table layout? Answer: In general, the rule is to keep like things together and separate out the unlike things to other tables. When you have clumps of things sorted out (think about sorting your laundry, for example), each clump probably belongs in a table. When you identify an individual kernel of knowledge, see to what it belongs. Is it a person's address? It belongs in the table that describes persons. Is it a COMPANY's address? It belongs in the table that describes companies. And so on. This is the nuts-and-bolts method of data normalization. Divide and conquer.

 

[Len Boorman]

Just picking up on The_Doc_Man's point about how many field is too many,

I wrote an application in a previous life that included Accident Reporting.

Now an Accident is an Entity (table) and how many Attributes (fields)can you think of that apply specifically to a specific accident. All Normalisation Rules to be adhered to.

I tell you I was surprised. Do not forget to include possibly involvment of Substances, vehicles, people, weather, location, First Aid, Hospital, Time off,

Yes you always get suspicious when the list gets a bit long and you go back over the list again and again.

Len

 

[Switchwork]

Follow up/

Nice one for all the advice much appreciated. Here is a list of fields that are in my table.

JobNo= Autonumber
RefNo= Unique number supplied with each job from customer
CustID= Autonumber on customer tble which is entered on to form
CustName= Name of Customer (company)
EmpNo= Autonumber on Emp tble which I enter onto form
JobDate= Date which job is done
Pick up= Place of collection
Destination= Place of drop off
Weight= Weight of cargo
NOP = Number of Pallets
Type of Van= initials entered to what type of vehicle ( TV=Transit van etc)
Drivers Earnings= Amount owner driver earns for particular job
Job Cost= Price of Job
Extras= Any extra on price of job eg. Waiting time
Total Cost= Price of Job manually added to any extra cost = Total Cost
POD= Proof Of Deliver- NAme of person signed for delivery (important)
Time Received= Time delivery was received (important)
Details Check= (Check box)- End of week check against paperwork.
COD= Cheque/Cash on Delivery ( Cheque or cash amount entered)
Notes= Anything relating to job in particulare.


All these fields are entered in by form which is easier. hope this helps in determining whether I can shorten the table.

Any ideas on the second table business?? Any info will do!!!

 

[KenHigg]

Switchwork, This looks like a data modeling issue (tables and their relationships), which should be driven by the business requirements. Can you share more on the nature of your business and what you are attempting to do with this db?


ken

 

[Mile-O]

Total Cost= Price of Job manually added to any extra cost = Total Cost

That field can be deleted and never seen again for a start.

 

[Rich]

That field can be deleted and never seen again for a start.

CustName too?

 

[Mile-O]

Yep. Never saw that one. My eyes were still glazed over.

 

[Mile-O]

I'd make a table for additional costs too. And a third table to simulate the many-to-many relationship.
One for Pickup Points and Destinations unless they are wholly different.