#1. It took me a minute or two to find the problem. In it's current form, your WallContacts table requires one record per person per wall. So, if Joe is the owner of a building with 2 walls, he will need 2 records in WallContacts (one for each wall). This is because you're storing the WallID in that table.
Try this: Remove the WallID from WallContacts. Create a new table:
Table: Walls_vs_Contacts (I can't think of a better name)
ContactID (FK)
WallID (FK)
This table will serve as the go between between WallContacts and WallsMaster. So when creating queries, you join WallsMaster to Walls_vs_Contacts to WallContacts.
#2. A less important issue is that your WallInteraction doesn't include ContactID. Also, WallInteraction table can't properly record interactions between the staffmember and 2+ people. If this is an issue worth fixing (that's up to you to decide), you'd have to make an additional many-to-many table like in #1:
Table: Walls_vs_Contacts (I can't think of a better name)
ContactID (FK)
InteractionID (FK)
#3. Depending on how many staff members you have, you might want to make a table with a StaffID and StaffName fields. Otherwise, WallInteractions.Staffname will fill up with "Sheryl","Sherri","Sherryl LastName","S. Lastname", etc. making it impossible to search for all interactions involving her.
Otherwise, you're off to a solid start. I like how you identified and properly named the unique keys in advance. (Oh, and if you think I'm wrong, I probably am.)
50MB is very small. I would not worry about size at this time. The DB can grow if you do a lot of coding in the DB, updating form designs, create new stored queries, and things like these.
If your queries are slow, it would not be due to DB size because Access tends to append data changes rather than handle data changes in place in many cases.
I would look into the indexing of all your tables. Look for any queries you are making that use WHERE clauses whose columns are not indexed. You may need to consider compound indexes for WHERE clauses with multiple colname=
combinations.
What concerns me is the number of columns in the HOUSES table. 60 columns? That is a lot of columns. Changes are the lenght of many of the rows are spanning longer than Access would like. Fetching just a single row from the HOUSES table is proabaly an expensive operation. You should look into denormalizing the HOUSES table. You could just go to the Database Tools
tab, and Click Analyze Table
.
In addition, you could just go to the Database Tools
tab, and Click Analyze Performance
and start measure other potential bottlenecks.
If the database starts to grow too large (over 1GB) or if you DB is in the frequent of doing heavy updates of rows, you should run Compact/Repair periodically. You may be surprised how much space will be reclaimed in doing so. You can do it in one of two ways:
- With the DB Open, Click File in the Top Toolbar, then Click Compact Repair Button
- Setup a Windows Task to Launch the Compact/Repair
Example
- MSACCESS.EXE is located in
C:\Program Files\Microsoft Office\Office14
- I have a Database Called
C:\LWDBA\RolandosDatabase.accdb
- Create a DOS Batch File called C:\CompressMyDB.bat with the following lines
BATCH FILE
@echo off
"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /compact C:\LWDBA\RolandosDatabase.accdb
Then just have a Windows Task kick off this batch file at the interval of your choosing
Best Answer
To normalize this properly, you need what is sometimes called a cross-reference or link table, to avoid making that country table so wide.
For example (this is for demonstration purposes only):
With this structure, when you add a new company, you add it to the
company
table, and then add a reference to it in the link table.So if you had 100 countries, you would have several hundred rows in this table.
I can assure you that querying this will be far more efficient than a very wide table.
When you want to query the data, you'll need to write an additional
JOIN
, but this makes managing the data so much easier, and less error-prone.Notice that I've added a
countrycode
column, which I would use for a two- or three-digit ISO standard (e.g. "USA", "CAN").This means you could even use the code as a foreign key instead of the
countryid
.