The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
I'm database stupid, and I need to get smarter.
zhen_rogue
Registered User regular
Registered User regular
I'm part of a small architectural studio (5-8 users) on both Mac and PC platforms.
We have a lot of "raw" client data - various bits like project locations, project start/stop dates, type of project, names of involved contractors, etc., that has been documented for the past several decades.
What we don't have, is all of this valuable data compiled into a single database that we can all search through.
Search queries of multiple project values - like;
- show me all the jobs completed in the last 3 years which took place in the American southwest and were restaurants
or
- show me all the jobs built by X contractor that were completed on schedule, and had a total construction budget of under $1M
or even
- show me all jobs between 2000 and 3000 square feet, that were lease spaces within an existing building, were built in the last three years, and were on the east coast
Problem is, I don't even know what to google, who to call, or what product i'm actually looking to purchase!
Can anyone please make me smarter about :
1. What professional(s) do I need to get estimates/quotes from on building the initial database from raw data?
2. What software/program will we, as the end users (Mac and PC) be using to search/update/modify the database once it's created? Is the database building/editing program different from what a user will use to search it?
3. Is this actually something "simple" enough that I should just download open office, and tutorial my way through hacking together an out-of-the-box database?
Thank you in advance for your insights and advice.
We have a lot of "raw" client data - various bits like project locations, project start/stop dates, type of project, names of involved contractors, etc., that has been documented for the past several decades.
What we don't have, is all of this valuable data compiled into a single database that we can all search through.
Search queries of multiple project values - like;
- show me all the jobs completed in the last 3 years which took place in the American southwest and were restaurants
or
- show me all the jobs built by X contractor that were completed on schedule, and had a total construction budget of under $1M
or even
- show me all jobs between 2000 and 3000 square feet, that were lease spaces within an existing building, were built in the last three years, and were on the east coast
Problem is, I don't even know what to google, who to call, or what product i'm actually looking to purchase!
Can anyone please make me smarter about :
1. What professional(s) do I need to get estimates/quotes from on building the initial database from raw data?
2. What software/program will we, as the end users (Mac and PC) be using to search/update/modify the database once it's created? Is the database building/editing program different from what a user will use to search it?
3. Is this actually something "simple" enough that I should just download open office, and tutorial my way through hacking together an out-of-the-box database?
Thank you in advance for your insights and advice.
0
Posts
I only have experience with one database (Progress OpenEdge), but Access or Oracle sounds more like what your looking to use. If you have the data in a format that could concievably be called easy to access then you need someone who can configure the database with a schema that defines your tables and fields. for instance:
customer table
has fields
name:
customer number:
billing address1:
billing address2:
(many more)
customer-job table:
has fields
job number:
customer number:
job address1:
job state:
final budget:
then you end up writing programs that do searches like
for each customer-job where final budget > 1,000,000 and final budget < 2,000,000.
find customer where customer-job.customer-number = customer.customer number
display customer.name customer-job.job-number final budget .
etc, etc.
So once you have a schema worked out, you take those easy to access files, which would be flat files , probably quote or comma seperated into the order of the fields you have listed in your schema, then load the data into the database in one swell fwoop. (It's usually not that easy but once you have a process down, it becomes that way).
Don't just blunder into making the database, it's a bit of an art to design them so they don't turn into a huge mess with lots of repeated and inconsistent data. But it's easy when you know how. Perhaps your company has budget to send you on a training course?
What you want is a project management system. That way, not only can you group together the existing data, but actively create and manage projects. Furthermore, such systems provide GUIs and tracking tools to actually make sense of the data in an accessible manner.
Two systems I can think of offhand are MS Project Server and FogBugz from Fog Creek.
zhen_rogue's examples were practically SQL pseudocode
That said, I'm unfamiliar with the explicit project management systems described, so I'm not sure if they would or wouldn't suit your needs more than something more custom built.
1. MS Access. Apparently easy enough to use that you don't need coding chops to put together a decent db, a front end for entry/manipulation, and a few queries. It costs whatever MS office costs. It's pretty limited as far as DB's go though and it does require a somewhat technical head and a sense of DB design to get the most out of it. Personally, I think it's kind of a pain in the ass. I haven't built anything in Access but I do support an access database one of my predecessors put together for one of our smaller departments. As soon as I have a spare minute, I want to get rid of it and replace it with a sql database and web front end. I don't know if you can hire people for contract Access jobs. I mean, I'm sure you can hire people to do anything, but I don't know if the market is big enough to find the right person to actually make you something you'll want to use.
2. Intuit Qbase. I really like this as a consumer product. Everything is hosted online by them. Easy enough to use that a novice can set up data entry forms and queries, but you still might want someone knowledgeable about databases to help you set up the actual database. It's not to difficult to build basic forms/pages and queries, but you can also use sql to build more advance queries. It seems like they also have decent customer support I think it costs $300 a month.
3. FileMaker/WaveMaker. These are both basically applications to let you build fully functioning custom database driven apps. Filemaker has a licensing fee whereas Wavemaker is open source. They both make it pretty easy to build a web/mobile app front end for a database, though it's still probably a bit much to take on if you don't have the right experience. There does seem to be a market of developers using one or the other who can build custom apps, and it's probably cheaper than doing a custom project from scratch. Once the database and app are set up, you'll have to host it either yourself on some server in your office or on space you rent on a webserver. I've played around a bit with WaveMaker, and it seems like a great option unless you need something specific that it doesn't support or need to make something pretty advanced.
4. Custom Job. You can also just hire someone to develop a custom app using mysql/php or any of the other open source DB/programming solutions. You'll have the most flexibility with this option, but it depends on how good a developer you get (and thus how much you're willing to pay). If you pursue this option, definitely ask to see other examples of work (also applicable to the FileMaker/WaveMaker solution). You basically get ownership to all the code so you can do whatever you want with it, but you also have responsibility for it. You'll want to work out some support agreement in case anything goes wrong or needs tweaking, either with the developer or a third party.
5. Out of the box solution. There's really not an out of the box solution for architecture firms?
The other issue you're going to have, possibly, is populating the database. How is your information stored now? If it's all in excel or some spreadsheet program, then it might be fairly straightforward to import into a database. If not, then you're going to need someone to do data entry.
It's a good sample db whose name I can't remember
Google "adventure works access" it's a decent example
I'll try and respond to the outstanding questions/issues:
1. I really like the idea of a front-end webpage and back-end DB.
This would also be useful, seeing as the principal travels often - he could feasibly access the DB through a secured web connection from any remote location?
We already have a server on-site which we can access remotely for various drawing files.
2. The data (unfortunately) exists in multiple formats, multiple machines, and even on paper in some cases. It will be a brute-force data entry effort for someone initially to compile all of the data.
Now, I don't mind doing this myself - but what format would be of the most universal use to whomever is going to build our DB? Would just a gigantic master .xls spreadsheet do the trick?
3. Budget - not a primary concern at this point. We're not Boeing or IBM, so it's not like this DB is going to need a team of coders working in shifts to generate. We're willing to pay for the simplest solution which meets our needs.
Please keep the suggestions coming; the more I can learn about what exactly we need, the more apt we are to hire the right person(s).
1. How many users?
2. How many records exist today? How many will be added each day?
3. Do you need to edit the records? Do you need to log or audit those edits?
As far as data format, once you get a design, you will need to brute force it into some xls/csv/txt file for an initial load, unless you want to pay for someone to build a converter (not worth it). My gut take is as follow:
1. If you're willing to live with VPN-only and have either a small amount of users or a small amount of volume, just go access for simplicity (note - I hate access, but that doesn't mean it doesn't have its place)
2. If you want more features, go and look at commercial offerings for similar systems, as there are a lot of fairly cheap CRM and quoting options out there that will end up being way more scalable and stable.
Thanks again for the detailed info.
I'm lost in the networking discussions (VPN, etc), but I can address the other items:
1. Maximum of 8 users, average of 5.
2. Project records would be around 1000, with approximately 10-15 data values for each project. Will likely add 50 similar projects/year on average.
3. Records will be edited/updated often, no logs/audits needed.
It seems MS Access (or similar program) might be a good solution, though some questions remain:
A. Would the initial brute-force effort need to develop a master .xls first? Or could the initial data entry task go straight into Access?
B. Once created, does the Access DB offer access to both MAC and PC users?
Again, thanks in advance for the clarifications. This is most valuable.
You can also develop forms to produce a budget "Front End"
Access is nice and easy to learn but does not necessarily scale to large amounts of data. (at least it didn't when I last used it 10 years ago). There are more sophisticated solutions for large databases.
Hint: don't make a new database for each project. Have them all in one database for ease of producing multi-project reports.
You can import files into Access or input data directly. Merging different files in Access can be sort of tricky IIRC but it can be done, so as long as your data is in .xls, .csv or some other format Access likes then the different files don't need to be put in manually. I haven't tried to use a Mac for this but I can imagine there are at least some compatibility issues. I'd second whoever said to build it in SQL and use a web page front end. You'll have to contract someone to do it but your finished product will be way more user friendly and cross platform compatible.
Does anyone have pros/cons/experience with using this software?
Sorry. "Use a program that understands SQL to build your database, because you can't just type code into Word and make it work."
Jesus.
Granted that it's good to get knowledge in this area, but if you have a little bit of time and are near a university with an IT faculty, see if there's any students who could use it for credit. I remember having assignments/projects that could be done using real-world examples. Kick them a few bucks and both sides make out like bandits.
DB stuff can be very rewarding but in order to do the design well it needs a lot of 'measure-twice, cut-once' so that's why I mention getting some help with it.
@schuss - Care to elaborate on why that program isn't a good idea? Unfortunately the boss (who is a mac user) heard it's the cat's meow from some management consultant. Then he saw the big, full-color graphs and charts and happy people on the website, and OH LOOK SHINY LET'S BUY IT.
Personally, I'm not sold on it - a fancy GUI doesn't mean it can actually do what we need it to do.
Filemaker pro tries to be everything, but it can only do each thing so well. That said, the databases I work with are all enterprise level (Teradata/SQL/DB2), so the user-level DB's like this aren't my forte. My main experiences with Filemaker were about 8-10 years ago and uniformly bad.
The task is typical enough that any database developer should be able to cope with it. I could do it myself and I have not touched databases since University.
Truth. Also, just learning the tools will take up so much of your time that it will be cheaper to just hire someone to do it.
Also, there is nothing worse than building a database, and finding out 6 months down the road that X person would have liked to have Z data point, and it wasn't included. make sure you've got as complete a population of data as you can get to start with.
Because if you read what the OP is saying, he's asking "how can I manage the projects my company has?" Yes, he could put all the data in a database. But then there's the issue of loading data from future projects from various employees and sources, making the data usable, training people to use the database, etc. Stuff that project management systems have already done.
In short, why reinvent the wheel? Yes, he originally asked about databases, but that was in order to solve his main problem. He's better off f getting a tool designed to solve the underlying problem than trying to build one himself.
This is why you should be looking at project management systems. Most, including the two I mentioned, are built around web-based front ends sitting on a database.
Maybe he was unclear in the wording of his post. He seemed to be wanting to do stats and queries about finished projects, not to manage the status of current projects. Two very different problems, with two very different solutions.
He seemed to want to answer questions like "How often did we go overbudget in the last 10 years?" (Database query) not "How much over budget is project X, and what can we cut?" (Project management question)
We need the front-end solution to be as idiot-proof as possible, because not everyone on staff is tech savvy.
I don't want to be the go-to guy that has a modicum of DB knowledge, and therefore becomes the de facto data entry drone and DB administrator.
Hopefully this clears up some of the user-based questions and my role in the process.
I can nix the suggestion of DB training for myself, we don't have the time and I don't have the interest.
It's starting to seem more like a custom-made DB scenario is the better option over a pre-packaged management program. I don't see that any of them allow for complex queries, they look like glorified spreadsheets with too much time wasted on report/graph generations with sparkly graphics.
Essentially, this DB solution would be used to run stats on completed project data. We don't need integrated calendars, timesheet/staff tracking, etc.
We use a shared google calendar for keeping tabs on deadlines. Very simple and effective.