table design

[scarface_jr]

I need a record method of tracking progress of a case file and who is responsible for progressing that file. Each file has a unique number associated with it. I will be looking at week on week progress where the weeks are fixed -week1-52. Each person is associated with a file and resolving the file before week52. Each person can transfer to different owners, and files can be resolved, in which case they have no owner. New files can also join the list throughout the course of the year, although it would be uncommon. Maybe a few hundred per week, verses 10k resolved per week.

I'm stuck between 2 different design ideas.

Design1:

File Table
------------------
File ID, Owner ID, week ID

Owner Table
---------------
Owner ID, Owner Name

Design2:

File Table
--------------------
FileID, week1_Owner, week2_Owner, week3_Owner ....... week52_Owner (53 fields total including fileID)

Owner Table
--------------------
OwnerID, Owner Name

Owner ID's are stored as single since I only have 25 Owners.

Design1 seems to make more sense. It also has potential to grow to a huge number of records. We currentlly have a little of 400k records. This means that every week we are adding close to 400k more new records every week, at least for the first few weeks/months.

Design2, seems a lot more inflexible, however I know the number of fields I require and that will not change. Each week should only be adding a few hundred maybe upto 1k records each week maximum. The only data I need to bring back should be current week. I will be storing totals for the week separately so I can see week on week progress.

thoughts/considerations. Leaning towards design2 as it should be more manageable and faster, however it just feels wrong.

 

[Fran Lombard]

Design 1 is the way to go.
Don't worry about record count with proper indexes you'll be fine.
Proper normalization of data structure will make for a simpler more efficient system.

 

[plog]

1 is better than 2, but I think you may have some errors with it as well based on the names you have given objects.

Files, should have its own table, independent of owners. Another table should exist to manage that. Also, week ID is a poor field alone, you will also need a year field. I believe this should be the structure based on what you have stated:

Files
File_ID, autonumber primary key
File_Resolved, Yes/No field


Owners
Owner_ID, autonumber primary key
Owner_LastName, Text
Owner_FirstName, Text

Assignments
ID_File, integer, foreign key to Files
ID_Owner, integer, foreign key to Owners
Assignment_Year, integer
Assignment_Week, integer

 

[scarface_jr]

you're completely right about the files table. Had it written down on my notepad but forgot to include it as it was not causing me issues.

One thing I am also concious of is size. I'm aware that access has a 2Gb limit per db file. The way I have stored the date is because of size considerations and the accuracy needed. 8 bytes for a date field, only one for a Byte field. Would creating a 'dates' table with a weekending date be a good idea as I can't imagine the business owners will be happy with week1, 2, ... week n on the report they see. As the report will be based on a weekly snapshot I would only need accuracy at the weekly level and 8 bytes seems like a waste of space. That way I would also not need the year field.

thanks for the guidance so far, been a great help

 

[plog]

I wouldn't have a dates table, I would just use a date field in Assignments instead of Assignment_Year and Assignment_Week fields.