I am in the process of designing a database app for a client of mine. I have it laid out with at least 36 tables. I am trying to downsize the database and make it less complex without losing functionality. About 8 of these tables are holding single fields. For example, I have a table called Test that contains various information including a Laboratory_ID that is connected to a Laboratory table which just contains names of laboratories. A friend of mine was saying there is some way to set up my Test table so that those values would be there sort of as a combo box without having to have a separate Laboratory table. Is this possible? I am also toying with the idea of making a static combo box on one of my forms that just lists the various labs without pulling them from a table. Any suggestions on the matter? Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
downsizing database layout
- Thread starter striker73
- Start date
My suggestion is don't be troubled by alot of tables. Particularly for combos. If you hard-code the record source in a form, when changes are needed you need to make design changes to the form. If the rowsource for the combo is a table you can code the combo, using the not-in-list event, to allow the user to add or update the list. Much better.
Further, the better normalized your database, the more tables (generally) you will have.
So all in all, if your design seems to call for a table, by all means put it in. You'll be happier in the long run.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
Further, the better normalized your database, the more tables (generally) you will have.
So all in all, if your design seems to call for a table, by all means put it in. You'll be happier in the long run.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
- Thread starter
- #3
No, you're better off, if you store data in only one place and reference it with a foreign key your data entry goes much faster and you'll find forms and reports easier to manage (you might find them a bit more complicated to set up the first time but usability will be better).
Any speed issues due to using queries to pull the data together will be negligable.
I would (no sarcasm intended) suggest that you look into some relational database design training/education.
By the way, what kind of laboratory does your client have? A couple of years ago I did work for a reference lab in California. We handled about 500 samples a day with automated data import, barcoded samples. With tests, assays, analytes, controls and standards, etc we ended up with hundreds of tables. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
Any speed issues due to using queries to pull the data together will be negligable.
I would (no sarcasm intended) suggest that you look into some relational database design training/education.
By the way, what kind of laboratory does your client have? A couple of years ago I did work for a reference lab in California. We handled about 500 samples a day with automated data import, barcoded samples. With tests, assays, analytes, controls and standards, etc we ended up with hundreds of tables. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
- Thread starter
- #5
Sounds crazy. The client is actually an emission tester for various machinery (Mobil, etc.) It's my understanding that they collect samples of data and send them off to labs for testing and analyzation. I am actually in a database class right now and it's helping some. It's just the last few databases I designed weren't as successful as I would have hoped. One database (designed for our company, not a client) had issues running over a network, so I've been wondering if I shouldn't be using Access over a network and if I would have better luck with something different. Also, I had designed a previous database for this exact client, but now they have come back with an entirely different specification that is 100 times more complex. I think I am just nervous about the sheer size of this database as compared to the ones I've done in the past. Thanks for the advice!
Hmm, different types of labs. My lab was a medical lab, patient blood samples.
When you're running across a network be sure to separate your database into a 'front end' and 'back end'. Back end will have tables and relationships, front end will have everything else with table links to back end. Back end sits on the server and front end on all client machines where users need it. This will dramatically cut network traffic. In general Access is OK on a network up to a point. If you are going to have more than a dozen or two simultaneous users you'll want to step up to sql server.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
When you're running across a network be sure to separate your database into a 'front end' and 'back end'. Back end will have tables and relationships, front end will have everything else with table links to back end. Back end sits on the server and front end on all client machines where users need it. This will dramatically cut network traffic. In general Access is OK on a network up to a point. If you are going to have more than a dozen or two simultaneous users you'll want to step up to sql server.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
TheWildHare
IS-IT--Management
To expand on 930Driver's post, I also suggest splitting in to a front/back end scenario. However, there are some caveats - when a user requests a record from a table or runs a query, the ENTIRE RECORDSET will be shipped to the user's machine for evaluation - not just the "answer set" - it's the nature of the JET engine - JET is not SQL Server or Oracle or any of the "heavyweight" database engines. So I wouldn't necessarily split a db for the sake of "less network traffic."
However, the housekeeping and maintenance pluses of a split db usually override any drawbacks.
- Try to keep as many static lookup tables on the front end, for one thing.
- Try to maintain persistent connections.
- Watch and plan for record locking scenarios - A2K can lock at a record level, but prior versions lock a 4K page, and you may need to plan for this.
I have built several dozen Access apps in the last 6 or so years, and split every one. It's almost a given.
Jim
However, the housekeeping and maintenance pluses of a split db usually override any drawbacks.
- Try to keep as many static lookup tables on the front end, for one thing.
- Try to maintain persistent connections.
- Watch and plan for record locking scenarios - A2K can lock at a record level, but prior versions lock a 4K page, and you may need to plan for this.
I have built several dozen Access apps in the last 6 or so years, and split every one. It's almost a given.
Jim
Jim,
If the front end is on the server as well as the back end, be it split or not, the forms, reports, macros, code all need to be pulled across the network! I definately split, and keep the front end on the client machine, to cut down on network traffic.
You are correct that the whole recordset gets pulled across. That's just the nature of the beast.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
If the front end is on the server as well as the back end, be it split or not, the forms, reports, macros, code all need to be pulled across the network! I definately split, and keep the front end on the client machine, to cut down on network traffic.
You are correct that the whole recordset gets pulled across. That's just the nature of the beast.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"
TheWildHare
IS-IT--Management
Exactly - the front end is on the client machine. I keep a "development" front end on the network that I use, and when necessary, shoot a new version down to each client.
What's nice about this arrangement is that it even allows for a client base with differing versions of Access - as long as the "back end" database is the lowest common denominator, you can get to it with A97, A2K or AXP...
Jim
What's nice about this arrangement is that it even allows for a client base with differing versions of Access - as long as the "back end" database is the lowest common denominator, you can get to it with A97, A2K or AXP...
Jim
- Status
Similar threads
- Locked
- Question
- Locked
- Question