CODEWITHSUNDEEP
ms-accessms-access-2010
natali
natali

Reputation: 90

Access table design

I have the following requirement for an Access table and I'm having trouble figuring out the best way to design the table. I'm not sure if what I have is the best practice in this case.

table fields are name, Status, Date each name will have minimum three status, and they change every month and then stop at Open. the date for each status change has to be captures in the database.

Example : 

Name    |   Status  |   Date  
--------+-----------+------------------
name1   |   N/A     |   April  
name2   |   N/A     |   April  
name1   |   Open    |   May  
name2   |   N/A     |   May  
name1   |   closed  |   June  
name1   |   open    |   July

Upvotes: 1

Views: 89

Answers (2)

John
John

Reputation: 1004

From the normalized point of view you need 4 tables total
1st table the master table 

Name    |   Status  |   Date (month ?)   
--------+-----------+--------  
1       |   1       |   4  
2       |   3       |   5

2nd table the names table 

ID      |   Names  
--------+-----------
1       |   name1   
2       |   name2

3rd table status table 

    ID      |   Status
    --------+-----------
    1       |   N/A
    2       |   Open
    3       |   closed

4th table Months 

ID      |   Month
--------+-----------
1       |   January
2       |   February
........|............
12      |   December

Upvotes: 1

Newd
Newd

Reputation: 2185

Honestly I have thought about this particular setup a number of times myself. I inherited a database that uses the format that you have posted.

While I don't think that setup is bad, another idea that I had was to create 3 checkbox fields that belong to each status to show progress. Then there would be 3 date fields, each one pertaining to when the checkbox was checked off.

That would cut the number of records down by 2/3rds but again. I think it is more of a matter of opinion.

Upvotes: 0

Related Questions