Find me better database software.
August 19, 2009 1:01 PM Subscribe
I have to compile a database for work to track about 300 separate data trends, but Access isn't cutting it. Suggestions for the next step up?
Bear in mind, I am not really a computer guy. I subcontracted this out to a programmer type who undertook to build the database but soon discovered that Access cannot handle more than 256 items in a table. All his solutions are pretty kludgy and he suggests looking for a more robust database program, but I have no idea where to begin. Cost is a factor in puchasing new software: I work for a not-for-profit and have pretty much used up most my 2009 budget for this project. Any suggestions? How many dollars am I looking at for SQL server?
Bear in mind, I am not really a computer guy. I subcontracted this out to a programmer type who undertook to build the database but soon discovered that Access cannot handle more than 256 items in a table. All his solutions are pretty kludgy and he suggests looking for a more robust database program, but I have no idea where to begin. Cost is a factor in puchasing new software: I work for a not-for-profit and have pretty much used up most my 2009 budget for this project. Any suggestions? How many dollars am I looking at for SQL server?
By 256 items, do you mean columns? That is easily fixed by splitting the data into two or more tables, each table having a single column in common.
Basically you have two tables that look like this:
Name|Age|Favorite Color
Bob| 13|Green
Bill| 14|Purple
Dawn| 16|Red
Name|Grade|Teacher
Bob| 7|Wilson
Bill| 8|Olson
Dawn| 10|Howard
Then you could relate the two name columns and easily pull a list of names with ages and grades.
posted by soelo at 1:19 PM on August 19, 2009
Basically you have two tables that look like this:
Name|Age|Favorite Color
Bob| 13|Green
Bill| 14|Purple
Dawn| 16|Red
Name|Grade|Teacher
Bob| 7|Wilson
Bill| 8|Olson
Dawn| 10|Howard
Then you could relate the two name columns and easily pull a list of names with ages and grades.
posted by soelo at 1:19 PM on August 19, 2009
MySQL is free. You can use the Administrator tools (also free), or phpMyAdmin (again, free) to look at your data, fancily. But you're gonna have to learn some SQL, which is pretty straightforward and easy. Welcome to the world of open source, my friend!
posted by Mach5 at 1:19 PM on August 19, 2009
posted by Mach5 at 1:19 PM on August 19, 2009
Seconding sanko's statement - I've been doing SQL for more than ten years, and I've never come across a situation that required more than 256 columns (or more than 100, for that matter).
Seems a little normalization is in order. I hope you didn't pay much for the subcontract.
posted by Pragmatica at 1:24 PM on August 19, 2009
Seems a little normalization is in order. I hope you didn't pay much for the subcontract.
posted by Pragmatica at 1:24 PM on August 19, 2009
also, soelo is talking about 'database normalization', and you're gonna want to have a unique ID for each name, not just the name, as your relational key. If your programmer doesn't know what that means, or anything about databases in general, you have other issues.
posted by Mach5 at 1:25 PM on August 19, 2009
posted by Mach5 at 1:25 PM on August 19, 2009
256 columns? Why not just use Excel? Sounds like that's all you're getting out of Access the way you're currently using it.
Or you could design the database properly.
posted by Civil_Disobedient at 1:34 PM on August 19, 2009
Or you could design the database properly.
posted by Civil_Disobedient at 1:34 PM on August 19, 2009
Okay, a quick primer in normalization (you could do this on your own, if you wished):
Make a Primary Table: Call it Table_Primary if you want, but it's going to be the table that contains the items you're tracking the 300 trends on. If it's a list of companies, or a list of people or what have you, doesn't matter. Important thing is you want an autonumber field, and a plain text field identifying the item.
Make a Secondary Table (Table_Data): Has three fields:
ItemID: This will be the autonumber field from table1
TrendTypeID: This will be an ID number from the third table, which we'll discuss shortly
TrendValue: Probably a text field, unless all the different types of things you're trending are numerical values
Make a Lookup Table (Table_TrendType): Has two fields:
TrendTypeID: An autonumber field (this goes in TrendTypeID on Table_Data)
TrendTypeDesc: A text field with a description of the trend type you're tracking
Basically, you end up with three narrow, deep tables instead of one very wide table.
Show this to your programmer type and see if it solves his problem. Unless you're dealing with more than 2-4 GB of data, there's no reason why Access wouldn't work.
posted by Pragmatica at 1:34 PM on August 19, 2009
Make a Primary Table: Call it Table_Primary if you want, but it's going to be the table that contains the items you're tracking the 300 trends on. If it's a list of companies, or a list of people or what have you, doesn't matter. Important thing is you want an autonumber field, and a plain text field identifying the item.
Make a Secondary Table (Table_Data): Has three fields:
ItemID: This will be the autonumber field from table1
TrendTypeID: This will be an ID number from the third table, which we'll discuss shortly
TrendValue: Probably a text field, unless all the different types of things you're trending are numerical values
Make a Lookup Table (Table_TrendType): Has two fields:
TrendTypeID: An autonumber field (this goes in TrendTypeID on Table_Data)
TrendTypeDesc: A text field with a description of the trend type you're tracking
Basically, you end up with three narrow, deep tables instead of one very wide table.
Show this to your programmer type and see if it solves his problem. Unless you're dealing with more than 2-4 GB of data, there's no reason why Access wouldn't work.
posted by Pragmatica at 1:34 PM on August 19, 2009
but soon discovered that Access cannot handle more than 256 items in a table
OK, this here is the first sign you may have contracted someone who's not really qualified to handle the task.
Firstly, Access can handle way more than 256 records in a table.
Secondly, if you mean columns, there are no good reasons to have 256 columns for a table.
Finally, if he's saying that you should be looking for a "more robust database program" but leaves it up to you to find what that is, he ought to be fired on the spot. You're not responsible for telling him what software to use. He's the expert. He tells you what HE wants to use, and you tell him whether you can afford it or not.
I suspect the guy doesn't have a clue and doesn't know the difference between Access and actual databases. No, Access isn't simply a database, it's way more than that. If you want to use a different database, you'll need to get a front-end programmed for your needs, and he should be telling you what languages or technologies he can use to do that. You sure as hell don't want to tell him to use PHP and have him learn it just before building your program.
Sum total, I think whatever you're paying the guy is way too much, as he seems to be not qualified and incompetent. This may be hard to take after actually throwing good money his way, but throwing even more money the same way won't end well.
posted by splice at 2:00 PM on August 19, 2009 [1 favorite]
OK, this here is the first sign you may have contracted someone who's not really qualified to handle the task.
Firstly, Access can handle way more than 256 records in a table.
Secondly, if you mean columns, there are no good reasons to have 256 columns for a table.
Finally, if he's saying that you should be looking for a "more robust database program" but leaves it up to you to find what that is, he ought to be fired on the spot. You're not responsible for telling him what software to use. He's the expert. He tells you what HE wants to use, and you tell him whether you can afford it or not.
I suspect the guy doesn't have a clue and doesn't know the difference between Access and actual databases. No, Access isn't simply a database, it's way more than that. If you want to use a different database, you'll need to get a front-end programmed for your needs, and he should be telling you what languages or technologies he can use to do that. You sure as hell don't want to tell him to use PHP and have him learn it just before building your program.
Sum total, I think whatever you're paying the guy is way too much, as he seems to be not qualified and incompetent. This may be hard to take after actually throwing good money his way, but throwing even more money the same way won't end well.
posted by splice at 2:00 PM on August 19, 2009 [1 favorite]
I'm a programmer who knows databases. splice is correct on all counts. Sorry, but it sounds like the developer you hired doesn't know his stuff. While I'm not a fan of Access and don't choose to use it myself, I know enough about it to know that it can handle more than 256 items in a table :)
Something like phpMyAdmin could work for you, but then you'll need someone who knows enough about php or installing open source apps to get it up and running for you. You'll also need to install MySQL, which is a free, great, robust database (and definitely more than you seem to need, but that's ok).
Some less-technical people also seem to like FileMaker.
However, Access should be able to do the job!
posted by jacquilinala at 6:02 PM on August 19, 2009
Something like phpMyAdmin could work for you, but then you'll need someone who knows enough about php or installing open source apps to get it up and running for you. You'll also need to install MySQL, which is a free, great, robust database (and definitely more than you seem to need, but that's ok).
Some less-technical people also seem to like FileMaker.
However, Access should be able to do the job!
posted by jacquilinala at 6:02 PM on August 19, 2009
There are a couple of issues going on here.
First off, I believe your database is designed improperly. That has been addressed in some of the other comments but it boils down to this: it is likely that you will have to redesign the table regardless of the database software you use.
Second, if you do really want to try a robust database, you could try SQL Server 2008 Express Edition (from Microsoft.) Be warned though, it does not have the same sort of interface that you are likely used to getting with Access. The only limitation is that you may only have 1 million records maximum. You may wish to purchase a full version of SQL Server if you require more than this.
There are also several free/open source databases you could use. MySQL has been mentioned but there are other options, such as PostgreSQL or even SQLLite, depending upon your needs.
I would agree with others here and suggest you revisit the design because I would bet my farm that Access can handle what you're trying to do.
posted by jlstitt at 7:20 AM on August 20, 2009
First off, I believe your database is designed improperly. That has been addressed in some of the other comments but it boils down to this: it is likely that you will have to redesign the table regardless of the database software you use.
Second, if you do really want to try a robust database, you could try SQL Server 2008 Express Edition (from Microsoft.) Be warned though, it does not have the same sort of interface that you are likely used to getting with Access. The only limitation is that you may only have 1 million records maximum. You may wish to purchase a full version of SQL Server if you require more than this.
There are also several free/open source databases you could use. MySQL has been mentioned but there are other options, such as PostgreSQL or even SQLLite, depending upon your needs.
I would agree with others here and suggest you revisit the design because I would bet my farm that Access can handle what you're trying to do.
posted by jlstitt at 7:20 AM on August 20, 2009
This thread is closed to new comments.
posted by sanko at 1:17 PM on August 19, 2009