One table per month or one long table?

[securitywyrm]

My appologies for the bad formatting of this question, I'm still not 100% sure I'm asking the right question.

I manage a database where I have about 40 hospital programs, each of which needs to send me a particular file once a month. I need to track when that file is received for any particular month.

Now the "Easy" way I can see to do this to have a single table with the primary key being the ProgramID, and each field being a month. However due to the 255 field limit, the database will stop working in 20 years. And as ridiculous as it sounds, this is a Federal hospital so it will probably be using literally the same system in 20 years.

So my question is, what ways could I future-proof this system? Creating a table for each month is a messy option. Creating a table for each year is an option as well, but that would involve coding that is more advanced than I currently know how to do. If there's more options out there, I'd love to hear them.

[June7]

One table, definitely. Fields for hospitalID, programORfileID, dateRec

[orange]

Tell us more about the 40 Hospital Programs. Typically you would have several tables - each representing a single Subject or Concept. These would be related to one another based on the "business rules" of your "business". We need more info to assist you with design.

What is a Hospital Program is 3-4 lines of simple, plain English?
What kind of information are you receiving/recording and what gets done with that data?
How volatile is the data? How often do Programs change? What growth in the number of participating Hospitals is expected?
etc, etc.

You don't need to be too concerned with the 255 fields/record limit.

One table for the receipt of a file from each hospital,Program, dated/timestamped. But if you pass that data to something else, or need to expand on Program, Hospital data etc, then more reference tables may be required.

[securitywyrm]

Tell us more about the 40 Hospital Programs. Typically you would have several tables - each representing a single Subject or Concept. These would be related to one another based on the "business rules" of your "business". We need more info to assist you with design.

What is a Hospital Program is 3-4 lines of simple, plain English?
What kind of information are you receiving/recording and what gets done with that data?
How volatile is the data? How often do Programs change? What growth in the number of participating Hospitals is expected?
etc, etc.

You don't need to be too concerned with the 255 fields/record limit.

One table for the receipt of a file from each hospital,Program, dated/timestamped. But if you pass that data to something else, or need to expand on Program, Hospital data etc, then more reference tables may be required.

1. The trainees at the hospital are divided into currently 43 programs. These are things like dermatology, neurology, general surgery, etc. This tracker is being built into a larger database that houses the contact information for each program. Eventually this larger database will absorb several other trackers such as the trainee qualification letters (A once/year process for each program), the affiliation agreements (once every ten years for each college on each program), the rotation schedules (once a year), and the meal cards (1/month for each trainee, 900 total but 250 at any given time).
2. Each month, each site director for a program needs to submit a letter confirming that the hours worked by the trainees matches the schedule on file. If it does not match, they need to submit a list of what changed. This is used for when we audit the bill from the university for the trainees.
3. Programs may occasionally become active/inactive, and one may be added every 3 years or so.

This is used for tracking what programs have submitted their confirmation letters for the month, so we know which programs to nag about it and identify an issue if one program misses several months in a row.

An issue I see is that even if I give each program its own table, it still has the 255 field limit which will be exhausted in 20 years using 1/month.

[ArviLaanemets]

Now the "Easy" way I can see to do this to have a single table with the primary key being the ProgramID, and each field being a month. However due to the 255 field limit, the database will stop working in 20 years. And as ridiculous as it sounds, this is a Federal hospital so it will probably be using literally the same system in 20 years.

This means you have monthly to add a new field into table. And then redesign your app (forms, queries, and reports) to take the new column into account.

Creating a table for each year is an option as well, but that would involve coding that is more advanced than I currently know how to do.

This means you have monthly to add a new table. And then redesign your app (forms, queries, and reports) to take the new table into account.

If there's more options out there, I'd love to hear them.

Third way is to have a table (or tables), where for every row you determine month, program, and other parameters.
You design your app and table in it once, and unless something changes cardinally, you can use the app for years without any changes except adding new entries into registries when this is needed.

And split your info based on it's functionality - this adds more flexibility to your app. E.g. you have a table where you register programs, and in monthly table you have program ID linked to this program registry table. When a new program is added, you register it, and that is all you need to do to allow to enter other info about this program.

[securitywyrm]

My objective is to future-proof it so that adding new tables and adjusting queries isn't needed.

I currently have seven processes to significantly automate. Once those five processes are automated to the point that someone without programming knowledge can consistently operate the system, I'll be getting promoted to a much higher position. If I have to come back every month, or even every year and update the database, I won't have done my job correctly.

[ArviLaanemets]

Once those five processes are automated to the point that someone without programming knowledge can consistently operate the system, I'll be getting promoted to a much higher position.

You are very young! Or very optimistic!

[orange]

I agree with Arvi.

Here is a link to various info in different formats re Database Concepts, Planning and Design.

I recommend you review some of the videos for general concepts (tables, relationships, normalization...)

Spend some time (~ 45-60 minutes) and work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link.
Pay specific attention to how he uses a description of the business to identify the parts of his database design and build a blueprint for his database. I can't over estimate the value of these tutorials and the procedure he uses to identify, design and test his model. Work through a tutorial or 2 to understand and experience the approach. Then do the same process for your own proposed database. You'll have a blueprint for developing your database. Much easier to do on paper than to start a trial and error approach with physical Access.
You have to actually do the exercise (work through) to get the benefit, but you will learn -and what you learn can be used with any database.

You may also get some insight from this link.

Good luck with your project.

[securitywyrm]

I agree with Arvi.

Here is a link to various info in different formats re Database Concepts, Planning and Design.

I recommend you review some of the videos for general concepts (tables, relationships, normalization...)

Spend some time (~ 45-60 minutes) and work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link.
Pay specific attention to how he uses a description of the business to identify the parts of his database design and build a blueprint for his database. I can't over estimate the value of these tutorials and the procedure he uses to identify, design and test his model. Work through a tutorial or 2 to understand and experience the approach. Then do the same process for your own proposed database. You'll have a blueprint for developing your database. Much easier to do on paper than to start a trial and error approach with physical Access.
You have to actually do the exercise (work through) to get the benefit, but you will learn -and what you learn can be used with any database.

You may also get some insight from this link.

Good luck with your project.

I'll try to allocate the time for that study. Unfortunately I need a solution "now" so here's my idea

I'll go with the 20 year approach. The columns will be ProgramID(key) and monthly in a YYYYMM format. That way the bits of code that have to integrate can use a date function and then query by form in the SQL to get the right field... I think. At this point I'm not sure if I'm just using technobabble or actual instructions.

[orange]

Not a good strategy, but it' your project.
And it seems you have lost focus on your other goal

Once those five processes are automated to the point that someone without programming knowledge can consistently operate the system, I'll be getting promoted to a much higher position