Table with alot of fields 3

[florida41]

I have 28 fields in one Table that is a Relational Database with 6 other Tables in the Database. I am using Access 2000. I need to add about 10 more fields to this Table that has 28 fields where it will now be 38 fields. Is this too big for a Table?

 

[RoyVidar]

Here are some of the maximum capacities of Access Microsoft Access 2000 and 2002 Maximum Capacities. The maximum number of fields in a table is 255.

Roy-Vidar

 

[dhookom]

Florida,
Access can handle that number of fields but I feel that adding more than about 20 fields suggests a normalization issue. We would need more information about your table structures to determine if adding more fields is adviseable.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[florida41]

Thanks what do you need to know about it?


I have 4 Tables with 1) Project Table (28 Fields) and 2)Customer Table (10 fields) and 3) Proj_Cust_Resource_Tech Table and (5 fields) 4) Maintenance Table (20 fields). Also have Feeder tables.

I want to make my project table bigger and adding 10 fields to it.

 

[lespaul]

What types of fields do you already have in the Project Table? What types of fields are you considering adding to the Project table?



Leslie

 

[florida41]

I have three number fields with one to many relationships to other tables and mixture of text, number, date and two memo fields.

 

[lespaul]

No, I mean like:

ProjectNumber
ProjectStartDate
ProjectName

etc.



Leslie

 

[willir]

So far, I have not seen anything suggesting a good or could be better design.

Having a table with a large number of fields, as suggested by Duane may suggest you may be skirting with nomalization issues, but then, it is possible that you actually do require the 10+ fields too. Access can handle this.

If we are to help, we need more meat instead of being speculative.

Here is a thought.
You should have a pretty good idea on the objective of your Project database.

From there, scope out what reports and forms you need. Do this on paper, in a rough sort of way. Then using your design, see if you can grab the information required. If you can - great! If you can and the data just "flows" off the tables - you have a spetacular design that meets your needs! But if you have to struggle - if you have to add this field and that field to accommodate this or that situation, then your design may have room for improvement.

Consider the following simple, and frequently made situation. An attendence database....

AttendenceTbl
EmployeeID
EmployeeName
MangerID
SupervisorID
WeekDate
Day1-OT
Day1-Sick
Day1-Late
Day1-Discipline
Day2-OT
Day2-Sick
...
Total-Hr-OT
Total-Hr-Sick
Total-Hr-Late
Total-Hr-Discipline

This type of design seems to capture all the data - I can grab hrs of this or that. But then when you start working with it using SQL queries, it becomes a struggle. (In fact, for the aforementioned database, there were numerous queries of 5000 to 7000+ characters long!!!)


The design breaks the fundenmental rules for normalization really badly. For example, because of the poor design, Day1 ... Day2 ..., the designer found he had to also include a total for the record because it was too awkward to handle the data and count hours otherwise.

How should be handled? One of many ways...

AttendenceTbl
AttendenceID
EmployeeID
SupervisorID
AttendenceDate
AttendenceType
AttendenceHRs

There are better solutions, but here I have removed the redundency
- why capture ManagerID each time? This is part of the Employee table
- why capture employee name - already have teh EmployeeID
- why have fields for Day1 to 7 when 99.9% are blank
- cacluations now can be be easily performed on the table instead of calculating for each record...
etc...

But why did I capture the SupervisorID? Because the employee works shifts, and may work for a different supervisor depending on the shift. I can justify why this information is kept on this table.

Notice how the table is cleaner, fewer fields. The common rebuttle to the "fewer" fields - well you will have many more records. Not a problem - every record will provide information as opposed to the first design where 99.9% of data was 0's.

If you start seeing duplication / repetition / redundency occurring here or there, you may be stretching the rules on normalization.

Richard

 

[dhookom]

A star for Richard. Thanks for taking the time to write a great reply.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

You are too kind Duane - thank you.

 

[florida41]

Thanks for all your info!

Here are my fields:
ProjectNumber
ProjectStartDate
ProjectName
history
ProjectHrs
EstimatedProjectHrs
ProjectRequirement
etc..

Some of the fields I want to add are:
ProjectDiskSpace //how much space on web server will project need
ProjectAssitanceNeeded //yes or no field
FTP_Account_Names //Names of People on this Project that need an FTP account

 

[lespaul]

Already I would recommend another table. If you are going to list multiple names of people who need an FTP account then you should have a table:

tblFTPAccounts
ProjectNumber (FK to ProjectNumber in tblProject)
AccountNumber (FK to the PK in the table that lists the people)

Leslie

 

[florida41]

Thanks but if I do keep all of my addtions in the Projects table what kind of problems might occur in the future because right now it works after I added all the new fields in the Projects Table.

Also this is a Cold Fusion application that is working with Access 2000.

 

[lespaul]

How are you going to store multiple people? If you're planning on a comma separated list:

Joe Blow, Susie Smith, Jane Doe, Tom Jones

then you will have MAJOR issues searching for people who have FTP Access.

And just because something "works" doesn't mean it's been done correctly.

Leslie

 

[florida41]

How else would I do this if not with a textbox?

I have an area to enter FTP access names but not sure how I would allow multiple names?

Can you advise?

Thanks!

 

[dhookom]

Developing for ColdFusion and other web based presentations is a bit different than Access. The table structures should be nearly the same (properly normalized) but the interface may vary significantly.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

you would need a table like I described above that has both the ProjectID and the Primary Key field of the table that has the list of names of people who will be able to have FTP access. You don't want to allow users to just enter the name because then you'll end up with mistakes:

John Smith
John M. Smith
John M Smith
Hohn Smith

if you have a list of people:

tblPeople
PeopleID
pFirstName
pLastName
pTitle

then you use PeopleID in tblFTPAccounts.

Check out 'Fundamentals of Relational Database Design' for more information on how to set up tables with one-to-many and many-to-many relationships.

HTH

leslie

 

[willir]

florida41

Thanks Leslie - I agree with the people or Account table. Florida (only in the Fall or Spring ) may later want to track active / inactive, expiration date, FTP account info, etc...

Florida

You asked about having too many tables, and Duane mentioned that you may have to "normalize" your tables better. There seems to be some truth in this.

However, what you are tracking is also pretty tough. You presented an itemized list of meaningful numbers - ProjectHrs, Estimated ProjectHrs, Disk Space, Start / End dates - all of which are very relavent the Project.

Now, something to ponder. Are these Projects really "projects" -- meaning that after you have completed the project, does the information remain on the server, still chew up disk space, and the customer still gets billed?? Or are these accounts?

You know your business -- I am just a body sitting somewhere in cyber world pondering your needs and objectives.

...But if they are accounts, you may be better off in the long run to re-think some of the design - yes have a project table, but some of the information could also be moved off to account related tables.

...Moving on
I see some of the information in your project table that can be moved off too. Leslie, quite accurately, has indicated that the FTP Account / People information would be better kept on a different table as a one-to-many relationship.

Likewise, the "History" feild interested me. Normally, when I see "history", I think of events on June 1, June 5, June 16. Again, possibly a one-to-many relationship.

Also, ProjectAssitanceNeeded - Yes / No. You actually have the ability of tracking the assistance that was provided - who provided how many hours on what day. I suspect this would be a many-to-many relationship since I suspcet the "assistance" could come several people who could help on different projects.

Leslie gave you a link to an important, and useful document which goes through the concept od relational database and the process of normalization. It is a pretty good read, and would go a long way in providing you with information in how can make informed decisions, and make use of the pwoer of relational databases.

Richard

 

[florida41]

I really appreciate all the information and time you all gave me on this!!!