February 20th, 2003, 12:06 PM
Creating Access DB - multiple value concerns, etc.
I am beginning the conceptual development of a database I will be creating in MS Access 2000. I only have a little experience with Access, most of my background in DB work being with MySQL.
What I am doing is creating a database to store all of our companies chargeback notification, response and result information.
So far I am looking at three different tables: entries, MAPs, and products. entries will store case and customer specific data like charge date, shipping addy, order date, delivery date, and so on. The entries table will require data from the MAPs table (Merchant Account Providers) including MAP name, address and fax number.
This is where my first question comes in. MAPs can have multiple addresses, and each address can have multiple fax numbers. I would like to have all MAP addresses and fax numbers stored to the DB to avoid manually entering these values, but I am debating whether to set up a third table 'MAPFaxNumbers' with MAPID--FaxNum columns, or to just use comma separated values to store multiple fax numbers when creating a new MAP entry. Benefits to either of these approaches?
Along the same lines, each entry in the 'entries' table can have any number of products - a disputed order may contain 5 products for all we know. I would like to have yet another table containing all of our products by ID, and be able to select from this table when creating a new 'entries' entry. I will need to be able to select multiple values from the products table, so would it be a better idea to create an entry/products relationship table or store multiple product IDs in a single field 'products' in the entry table?
Our main concerns are this: need to be able to search the db and pull records by date, products ordered, MAP, charge amt. and more. Basically want to be able to create dynamic reports based on all of this data.
Thanks for any input I will be interested to see how you would tackle this
February 20th, 2003, 11:17 PM
I've designed a number of databases with issues similar to yours, many of them using Access. The overall theme of your questions seems to be whether to increase the number of tables (better normalization) or to keep your table structures simpler by increasing your record size and doing creative things with your columns. My suggestion is to normalize your data as much as possible, which usually means more tables. It often seems tempting to cheat, and is sometimes tiresome to create 'all those tables'. However, good design up front will make your life MUUUCHH easier in the long run. I spend a great deal of my time cleaning up messes from poorly designed Access databases.
For your situation, my advice on all counts is normalize! If multiple values can occur which are linked to the same piece of data (i.e. fax numbers to MAP addresses, products to orders/entries), create a new table for that data. In the long run, you'll be glad you did. Your data will be cleaner, easier to search, harder to screw up and easier to maintain.
Access often gets a bad rap, a lot of which is undeserved. It has some very nice rapid design features, can generate very friendly and intuitive user interfaces and has robust, built-in reporting. It also has good support for referential integrity, including cascading updates and deletes. However, there are a few real limitations to consider before choosing Access for an important project.
1.) Access does not do well in a multi-user environment. It slows down (a lot!) as more users hit it. Corrupted data and wierd problems become more likely. If you are going to have more than 3-4 users hitting it, you may run into trouble. If they are going to be hitting it heavily, even that number may push it.
2.) Access begins to struggle with large databases, particularly in a multi-user environment. What's large is relative, and good design helps. Although I've built and used larger databases with Access, in my experience at somewhere around a 100,000 records in a given table, you can begin to see some noticeable performance issues, even on a local machine.
3.) The Access security model is actually pretty complicated. To secure an Access database can be an adventure the first few times you do it! Also, Access security is not great. It's pretty easy to compromise or steal data even in a 'secured' Access database.
If you can live with those limitations, then go for it. You can certainly create a fully functional, well-designed database, complete with user interface, in a very short time period. If you're coming from mySQL, you may also find the referential integrity support in Access very useful for keeping your data clean, and preventing accidental deletions of key data.
Hope this helps. Good luck!
February 21st, 2003, 02:40 AM
Thank you dkj083063, that definitely helped!
Honestly, I would like to normalize as much as possible - for most of the reasons you listed. So I'm happy to hear you think this is good practice
The database is infrequently accessed: it will mostly be me, with perhaps two other people generating reports from data there. But I have a feeling I will end up doing most of them
It is behind a firewall and will stay there. I'm pretty much stuck with Access since my company is afraid of words like "PHP" and "MySQL", so it's not like I have a lot of flexibility anyway.
For the past 1.5 years all of our chargeback data has been stored in a .DOC file and used ONLY for mail merges to generate teh responses. After following this stale process for probably a year now I decided to redo the entire process.
I can't seem to import the .DOC format into Excel or Access at ALL. I can only seem to retrieve the first half of the columns in the db and the rest are totally corrupt. In fact, the only way I was able to view them and generate any kind of data with the info was with PHP
I'm actually havng some fun with Access though, it doesn't seem all that bad right now. If you can recommend any good sites on the subject to help me hone my skills it would be higly appreciated
Thanks again for the help and I'll be sure to be back bugging everyone again if I run into problems
February 21st, 2003, 03:00 AM
Its really sad that companies are like that.
My company has a large access database that manages all the clients and sales. The front-end that sits on each users machine weighs in at 20mb. My work are eager to convert the system to MySQL and PHP. Companies really shouldnt rely on access for their data. Its NOT a commercial solution.
Just looked through my bookmarks, to learn how to do funky stuff with Access see this link http://www.mvps.org/access/toc.htm
Last edited by a.koepke; February 21st, 2003 at 03:02 AM.
February 21st, 2003, 10:44 AM
It is sad I think Access will be a reasonable choice for this particular project, though. Not the best necessarily, but it's not a major part of the company. I shouldn't even get into the rest of the IT we use here, it's so gawky and difficult. I would love to pull customer records directly into Access from our FileMaker DB but alas IT is too lazy to implement the necessary scripts and FM5 has lousy ODBC support. Everything is 'tomorrow' for these guys, they seem to enjoy having bulky, incompatible systems in place; there are numerous applications using the same data but each one has its own way of getting at it. There is no real infrastructure in place here, but I guess that's ultimately their problem.
Anyway thanks for the linkage, looks very helpful. We'll see how things progress!
February 21st, 2003, 11:15 AM
From what you have said the IT guys sound like a bunch of trolls. I am the only IT person at my work so what i decide goes (as long as managemet approve) I quite like it like that
February 21st, 2003, 11:47 AM
Well there are two IT guys - CIO and sys admin. They are both very friendly, and both act like they want to make things better, and give me the chance to do that for them - but the CIO blows me off regularly Upper management is very greedy, as is the entire mentality of the company right now. I don't think mgmt. realizes how much time gets wasted on repetition and slow technology, and just how inexpensive and potentially beneficial it would be to change that. IT just doesn't seem to care much beyond securing their own job; they pretty much bow down to management instead of being proactive.
Sounds like you are in a pretty good position, can't wait until I'm not stuck in such a stale job
February 24th, 2003, 10:42 AM
You know, if you and maybe one other person are going to be using the database, then I don't really think you're going to run into problems. To me, Access is THE solution when you have an IT department that doesn't have the interest or capacity to adequately serve a small customer's needs. If you're not depending on it for the wrong things (huge databases, multiple concurrent users) it is a great product. With smaller datasets and limited users, it's fast and easy to manage. Just design it well!
Just to give you some comfort (because EVERYONE seems to gripe about Access and tell horror stories..) one of the Access databases I wrote stored and managed all the drug and alcohol test scheduling and results data for the second largest railroad in the US. It was intended was a temporary solution until the IT department implemented the 'preferred' solution. However, as often occurs in the grand corporate scheme of things, it was two years before the 'real' solution came along! The Access db was used by about five different users, and it imported text files for test scheduling, drug test result files, employee files, and did variety of e-mail notifications and statistical reports. It had over 500,000 records in it and was about 200 MB before it was finally phased out. It worked the whole time, and never lost or corrupted any data. It had gotten pretty slow by the time it was replaced, but it DID WORK!
I would suggest going an getting one big, thick book on Access. Sit down in the bookstore and look through the index to see if it has topics that interest. Read a few and see if you like the way it's written. Find one you like and buy it. I use the web heavily, but I also find that it's often faster to find what I'm looking for in a book. Low-tech of me, I know
Anyway, good luck and have fun with Access. It may not be an "enterprise" solution, but, as with most things, good design and smarts can go a long way. I've seen completely hideous things done with Oracle, too! Party on!
February 24th, 2003, 11:03 AM
Well to be honest with you, I agree that Access should be just fine for what I'm doing. Really, my only complaints about IT ignoring some of the alternatives are regarding our Intranet (or, lack thereof ) The Access DB won't get real big for a long time; and considering 99% of the data pulled will be used in Office it's kind of a shoe-in. I just hope I do design it right!!
WOW 200MB? Well I guess that done correctly Access can handle a relatively large load - - luckily I don't think we will have that many records. Even if it did work, I don't think I'd like using Access with that much info considering its speed - - but I do get your point that it's not completely useless with big records
Hehe, well if you're low-tech I guess I am too. I spent my sunday afternoon at Borders reading through Access books. Didn't buy anything, but I got some ideas and I'll probably pick up a book on it when one really catches my fancy. I find that if I am truly interested in learning something, a book is a must.
Thanks again for the great help dkj, again I'll surely be back complaining if I can't figure something out