Advice Sought on Best Practice for Database Construction

[Travers01]

Hi All,

I am a complete Access newbie in the process of creating a database for my work. Currently my team of 10 employees use six different databases and spreadsheets to record various types of information and the process is quite time consuming. I bought a number of 'Dummies Guide to Access' style books and completed a fair bit of research on the net and have now designed an all-in-one Access 2003 database. What the books tend not to explain, however, is some of the underlying principals as to what makes a good, efficient database. This brings me to my questions, which I have listed below after trying to explain a bit about where I am currently at.

I have created a database with a separate table for each of the six existing databases / spreadsheets. A switchboard allows the user to select the one they need. Currently the existing databases used are very slow when loading, entering data and searching. I am concerned that combining all six together in my new database will slow things down further. My goal is to create a database to record all of our information that is not too slow use.

In terms of data to be stored, one table will store approximately 2800 entries per month, the second will store approximately 1100 per month, approximately 700 per month for the third, 100 per month for the fourth, 50 per month for the fifth and only one or two a month for the 6th database, as its only a place to record certain rare events. I'm not particularly sure whether this is considered a lot of information for an Access database to manage.

Specific Questions:

#1. Am I correct in my understanding that if I split the database into front and back-ends, this helps speed things up as the data isn't loaded each time a person opens the database?

#2. Would I be best to leave my database with 6 main tables to record the above amounts of information or have a database for each (or possibly 3 large ones and 1 smaller one for the other 3 tables), linked by the front-end?

Any information or advice on the above questions would be greatly appreciated.

Thank you for your time.

Kind Regards,

Travers

 

[vorn666]

Hi Travers,

1.
In my opinion splitting the database is a good idea as long as you are moving it over a quick enough network. My original database was slowing down day by day till i split it, it suddenly got a chunk slower but has never slowed since. Like yours sounds it will mine tends to grow at an alarming rate so I would recommend it.

2.
How many tables you need may be determined by the number of fields on each, if each current table has only a few field you will be able to combine, but not if there are too many. if you do combine some tables, it is efficient to then split them up using queries before you then display the data in forms. I suppose the real question is whether the current tables have any link to each other, if they are causing a duplication of input they should be combined, if they are unassociated matters what do you hope to achieve.

Hope that is of some help.

I would say the principle of an efficient database is simply the least duplication of data the better, and programming it so it is idiot proof (the first saves time and money, the second saves mistakes/time and money)

 

[The_Doc_Man]

Issues to consider:

1. That growth rate is not so bad. The longest records you could have would be 2048 bytes for each of six tables. 2800+1100+700+100+50+2 = 4752/month = roughly 8 Mb per month of data growth. If you have a large-enough hard drive, you can reach the 2 Gb limit in, oh, 250 months. Ten years, ball-park. Pardon me if I don't stick around to check up on you as this nears its end-of-life cycle. Further, I'm betting that your records aren't all 2048 bytes long. Probably a LOT less than that. So I would not fear the size.

2. Of greater interest is this: If you have data that is valuable to your business, IMMEDIATELY think of backup and compaction as a way of life. Set aside some time to clean up and back up your database every so often. It is up to you as to how much data you can lose before it hurts too bad. Figure out what that number is, then figure out how long it takes to get there, then cut that time in half and that is your best quick shot at frequency of backup and compaction. Compaction and backup together assures you of TWO things: (a) database can be recovered (b) Access "working slop" (which causes database bloating) is cleaned out regularly, which contributes to faster DB response all around.

Also consider whether there is an ARCHIVING issue. If your data gets more than 2 years old (5 years? 10 years?) is there a time when you can purge it or move it to an external table for safe, accessible, but NOT active storage? Data retirement or archiving is something you could search in this forum and not come up empty-handed.

3. If you have six different events that have nothing in common, six tables makes sense. If the six different things have a LOT in common, reduce the number of tables by storing an event-type code in a table that is the new home of the tables that look alike. The rule of thumb I uses is that if the only difference between two tables is, say, less than 5% of the fields required for the "common" table that would hold them both at once, and particularly if the differences are text fields or short integers (byte,word) then slap 'em together. UNLESS the data USAGE happens to not be compatible. Because it isn't CONTENT that defines table structure alone. You also have to consider usage.

(The reason it is OK to have some sparse text fields is because Access doesn't store fixed-size fields like some other DB managers do. So storing)

4. The next question is PURPOSE. To design a database, there is an implicit PURPOSE to the activity. You are doing this because you want/need to do something with the data. You want to track it (obviously). You might wish to analyze it. You might wish to generate monthly reports. You might wish to determine some kind of performance for the people involved in the report. It isn't clear which of these apply. BUT you must decide the purposes (plural) of your DB and apply the "Old Programmer's Rules."

#1. Access won't tell you anything you didn't tell it first. Meaning: If you want to report on the number of XYZs you have in the database, you had better include a way for Access to IDENTIFY the XYZs. If you want to identify the dates of certain events, you have to store the dates somewhere.

#2: If you can't do it on paper, you can't do it in Access. Meaning: First, determine your purpose. Second, make a plan of how to get there. Third, for each element of the purpose, try to do it by hand based on written/drawn diagrams of your data elements. See what Access is going to have to do before telling Access how to do it. If you have to find the number of left-handed veebilfetzers but handedness and product names aren't in the same table, see how hard it is to link the two. That might give you some valuable feedback on your design.

#3: Give Access permission to help you find things. Meaning: Not only should you have a primary key, which always has an index, but you should also look into indexes for the most common fields you will involve in a search, with the following warnings: Long text fields are not good items to index. Integer fields, dates, and encoded fields are great for indexing. Short text fields are OK. Short = up to about 8 characters. Up to that point, dates are more expensive to index. After 8 text characters, text is worse to index than almost anything else. But there is a trade-off. Access doesn't let you have more than 10 indexes per table. And I would stop at 3 or 4 for most tables. But indexes really pick up performance when nothing else will.

#4: Don't assume Access will do ANYTHING right. Assume it is dumber than a box of rocks. (Which it is!) Meaning: Your DESIGN is going to make or break the project. Access is just the tool. The project depends on the craftsman, not the tools.

You admit to being a newbie. I would also suggest, if you haven't done so already, reading on "Database Normalization." Wikipedia has a nice article. Access help also has some pages you can read. Finally, your favorite search engine will find lots on "Database Normalization" if you try that. For the latter, I would limit my selections to the .EDU domain sites that have articles on the subject. Maybe a couple of .COM sites for vendors you know and love/hate. (Yes, database packages get that way... love/hate relationships are quite common.)

Finally, remember this old adage: A thing well worth doing is worth doing well. Spend some time on the design of this puppy. Make it a priority to not "shoot from the hip" at this target any more than minimally necessary.

 

[Sweetnuff38]

Nice,

Thanks for the info~!

Tracy

 

[MagicMan]

Great advice from the Doc Man.
I agree Normalization is the first thing to learn and apply.
I also strongly agree, anlayse...ask dumb questions EG: why do you need this, can we do without that? If you use the threat of gettting rid of these fields, you receive great explanations for the fields under scrutiny, instead of "Is it really needed?" which usually brings you only a yes. When I asked the user community which of the 300 reports printed daily, could be removed, I received 3 answers which allowed me to stop printing 5 reports. When I stopped printing all the reports, I receive 50 messages demanding 75 reports, and after a few questions, it was brought down to 65 reports. The other 230 reports were never missed. People believe they need something if they get it, even though they do not use it.
Ensure you know the dependancies (EG:if address is present, name must be present).
Validations (what values are acceptable, what values are not, what are mandatory).
Default values for the fields.

PS: Once you have defined the tables you believe you need, and start designing the forms, you will find you are missing fields, and have fields that make no sense. Change your tables, do not get stuck with the idea that what you have is too hard to change. It is better to Do It Right than try to get around deficencies. Access has a make table query, which can be used to add fields to existing tables easily. And it will not be just once!

 

[gemma-the-husky]

the docman touched on this already, but the importance of this may have got lost in the other important stuff he mentioned

now, the data content you mentioned

ie
type 1 data, 2800 per month
type 2 data, 1100 per month
etc

data from a single table is far easier to manage, so depending on what this data includes it may be better to have one table, rather than several. If its all similar stuff then keep it together. Don't think you need to keep each year's data separate - just chuck it all in the table, then tell Access you just need data between a certain date range.

in fact if some of these are summaries of other data - say you have monthly summaries, quarterly summaries and annual summaries then in fact you dont need to store any of the summaries AT ALL, as you can reconstruct them from the basic data. Although Access looks (and behaves in many ways) like a spreadsheet, it isnt a spreadsheet - its far more efficient, and far more powerful for managing large sets of data

with regard to capacity, Access will store far more rows of data than excel, and will process them far quicker than excel. A97 will handle a dbs of 1Gb, later versions 2Gb.

For normal purposes, dbs size just isnt an issue.