Database Planning
-
On the last project I was on, we utilized MS Access and while I was for the most part able to get around in Access, I found Access performed very, very slowly for even simple tasks once the DB grew to a certain size, and I was not at all impressed with the Report builder as I found it was almost always easier to export the data to Excel to generate my reports. Fast forward a few months and I am about to start a new project and I have been given quite a bit of say in how we set up our database. I do have a vague idea of how I would implement it but I am very curious to see others opinions as well to make sure I have all bases covered. Here are the 'contraints' if you will for how the database will need to perform. 1. There will be a few technologically savvy employees (approx 4-5) on site and the remainder of the workforce will be fairly illiterate, technologically speaking (they will be able to surf the web, do data entry in spreadsheets, type documents, but not much else). All employees will need to be able to access the data from reports easily and quickly without having to memorize commands or know how to navigate a database etc. My fellow techies have experience mostly with MS Access, and a couple know SQL, I am more familiar with NoSQL type frameworks, but have a basic understanding of SQL and can get by in such a system. 2. Data will come from 4 sources. a.) Primarily, most data will come via spreadsheets. These spreadsheets will often be fairly complex with multiple tables, hidden columns and rows, formulas, reference cells, etc. b.) The next largest chunk of data will come from field reports hand written by the foreman on site. c.) The next largest chunk will come from simple verbal communication, or emails, word documents, or even typed letters. 3. Data will need to auto-generate reports. The more autonomous this can be made, the better. We might have as many as 10 different daily reports as well as hundreds of weekly, monthly, and yearly reports that we will have to generate. Ideally, this could be performed by non-techie users simply by providing date ranges or other simple constraints, and having templates scoop the required data from the database. 4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations, and preferably will not interfere with it's usage at all. It needs to scale well, be reliable and not be subject to data loss, or easily let data fall between the cracks. In terms of importance I believe it shou
-
On the last project I was on, we utilized MS Access and while I was for the most part able to get around in Access, I found Access performed very, very slowly for even simple tasks once the DB grew to a certain size, and I was not at all impressed with the Report builder as I found it was almost always easier to export the data to Excel to generate my reports. Fast forward a few months and I am about to start a new project and I have been given quite a bit of say in how we set up our database. I do have a vague idea of how I would implement it but I am very curious to see others opinions as well to make sure I have all bases covered. Here are the 'contraints' if you will for how the database will need to perform. 1. There will be a few technologically savvy employees (approx 4-5) on site and the remainder of the workforce will be fairly illiterate, technologically speaking (they will be able to surf the web, do data entry in spreadsheets, type documents, but not much else). All employees will need to be able to access the data from reports easily and quickly without having to memorize commands or know how to navigate a database etc. My fellow techies have experience mostly with MS Access, and a couple know SQL, I am more familiar with NoSQL type frameworks, but have a basic understanding of SQL and can get by in such a system. 2. Data will come from 4 sources. a.) Primarily, most data will come via spreadsheets. These spreadsheets will often be fairly complex with multiple tables, hidden columns and rows, formulas, reference cells, etc. b.) The next largest chunk of data will come from field reports hand written by the foreman on site. c.) The next largest chunk will come from simple verbal communication, or emails, word documents, or even typed letters. 3. Data will need to auto-generate reports. The more autonomous this can be made, the better. We might have as many as 10 different daily reports as well as hundreds of weekly, monthly, and yearly reports that we will have to generate. Ideally, this could be performed by non-techie users simply by providing date ranges or other simple constraints, and having templates scoop the required data from the database. 4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations, and preferably will not interfere with it's usage at all. It needs to scale well, be reliable and not be subject to data loss, or easily let data fall between the cracks. In terms of importance I believe it shou
Sea_Sharp wrote:
1. All employees will need to be able to access the data from reports easily and quickly without having to memorize commands
That means you need a UI. That probably means you'd need to write one, generate one, or use Access as your front-end.
Sea_Sharp wrote:
2. Data will come from 4 sources.
The database doesn't care where the data comes from. A is a simple import-operation. B and C cannot be automated, someone will have to enter the data. D is missing.
Sea_Sharp wrote:
3. Data will need to auto-generate reports.
Data doesn't do things, it just sits there. Databases are also usually not responsible for generating a report; you will probably need to create a report-template that gets filled dynamically, with a filter for each property. I'd put the database in SQL Server, link from Access to it, and exclaim it's "done". Access has a low learning-curve and everyone could click together the reports as they see fit. Excell could probably do the same.
Sea_Sharp wrote:
4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations
Backups do not prevent errors; any error gets backed up along with the rest of the data. Any major database-server supports backup-operations.
Sea_Sharp wrote:
In terms of importance I believe it should be arranged in the following hierarchy
It doesn't work that way. There's a comparison of database-servers here[^] - take a look at the capabilities, compare them to your needs, pick one. And make sure it is SQL Server that you pick. SQL Server Express is free, you could design your db in Access (and have a working Access-db as a backup if this experiment fails), and simply import it in SQL Server when you're ready (using the upsize-wizard in Access) "No bugs" is something that can never be guaranteed in complex systems; there's however a difference in patch-frequency. I know most people disable their auto-update, but I kinda like being updated regularly. You can also safely assume that databases sto
-
Sea_Sharp wrote:
1. All employees will need to be able to access the data from reports easily and quickly without having to memorize commands
That means you need a UI. That probably means you'd need to write one, generate one, or use Access as your front-end.
Sea_Sharp wrote:
2. Data will come from 4 sources.
The database doesn't care where the data comes from. A is a simple import-operation. B and C cannot be automated, someone will have to enter the data. D is missing.
Sea_Sharp wrote:
3. Data will need to auto-generate reports.
Data doesn't do things, it just sits there. Databases are also usually not responsible for generating a report; you will probably need to create a report-template that gets filled dynamically, with a filter for each property. I'd put the database in SQL Server, link from Access to it, and exclaim it's "done". Access has a low learning-curve and everyone could click together the reports as they see fit. Excell could probably do the same.
Sea_Sharp wrote:
4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations
Backups do not prevent errors; any error gets backed up along with the rest of the data. Any major database-server supports backup-operations.
Sea_Sharp wrote:
In terms of importance I believe it should be arranged in the following hierarchy
It doesn't work that way. There's a comparison of database-servers here[^] - take a look at the capabilities, compare them to your needs, pick one. And make sure it is SQL Server that you pick. SQL Server Express is free, you could design your db in Access (and have a working Access-db as a backup if this experiment fails), and simply import it in SQL Server when you're ready (using the upsize-wizard in Access) "No bugs" is something that can never be guaranteed in complex systems; there's however a difference in patch-frequency. I know most people disable their auto-update, but I kinda like being updated regularly. You can also safely assume that databases sto
Eddy Vluggen wrote:
And make sure it is SQL Server that you pick
In other news, Kim Jong Un got 101% of the votes. :)
Wrong is evil and must be defeated. - Jeff Ello[^]
-
On the last project I was on, we utilized MS Access and while I was for the most part able to get around in Access, I found Access performed very, very slowly for even simple tasks once the DB grew to a certain size, and I was not at all impressed with the Report builder as I found it was almost always easier to export the data to Excel to generate my reports. Fast forward a few months and I am about to start a new project and I have been given quite a bit of say in how we set up our database. I do have a vague idea of how I would implement it but I am very curious to see others opinions as well to make sure I have all bases covered. Here are the 'contraints' if you will for how the database will need to perform. 1. There will be a few technologically savvy employees (approx 4-5) on site and the remainder of the workforce will be fairly illiterate, technologically speaking (they will be able to surf the web, do data entry in spreadsheets, type documents, but not much else). All employees will need to be able to access the data from reports easily and quickly without having to memorize commands or know how to navigate a database etc. My fellow techies have experience mostly with MS Access, and a couple know SQL, I am more familiar with NoSQL type frameworks, but have a basic understanding of SQL and can get by in such a system. 2. Data will come from 4 sources. a.) Primarily, most data will come via spreadsheets. These spreadsheets will often be fairly complex with multiple tables, hidden columns and rows, formulas, reference cells, etc. b.) The next largest chunk of data will come from field reports hand written by the foreman on site. c.) The next largest chunk will come from simple verbal communication, or emails, word documents, or even typed letters. 3. Data will need to auto-generate reports. The more autonomous this can be made, the better. We might have as many as 10 different daily reports as well as hundreds of weekly, monthly, and yearly reports that we will have to generate. Ideally, this could be performed by non-techie users simply by providing date ranges or other simple constraints, and having templates scoop the required data from the database. 4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations, and preferably will not interfere with it's usage at all. It needs to scale well, be reliable and not be subject to data loss, or easily let data fall between the cracks. In terms of importance I believe it shou
Eddy's answer is deep and clean, but I would add one more - don't do it alone. Not at the beginning at least. Hire some expert to help you with the design phase and put the project on the right tracks...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
Sea_Sharp wrote:
1. All employees will need to be able to access the data from reports easily and quickly without having to memorize commands
That means you need a UI. That probably means you'd need to write one, generate one, or use Access as your front-end.
Sea_Sharp wrote:
2. Data will come from 4 sources.
The database doesn't care where the data comes from. A is a simple import-operation. B and C cannot be automated, someone will have to enter the data. D is missing.
Sea_Sharp wrote:
3. Data will need to auto-generate reports.
Data doesn't do things, it just sits there. Databases are also usually not responsible for generating a report; you will probably need to create a report-template that gets filled dynamically, with a filter for each property. I'd put the database in SQL Server, link from Access to it, and exclaim it's "done". Access has a low learning-curve and everyone could click together the reports as they see fit. Excell could probably do the same.
Sea_Sharp wrote:
4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations
Backups do not prevent errors; any error gets backed up along with the rest of the data. Any major database-server supports backup-operations.
Sea_Sharp wrote:
In terms of importance I believe it should be arranged in the following hierarchy
It doesn't work that way. There's a comparison of database-servers here[^] - take a look at the capabilities, compare them to your needs, pick one. And make sure it is SQL Server that you pick. SQL Server Express is free, you could design your db in Access (and have a working Access-db as a backup if this experiment fails), and simply import it in SQL Server when you're ready (using the upsize-wizard in Access) "No bugs" is something that can never be guaranteed in complex systems; there's however a difference in patch-frequency. I know most people disable their auto-update, but I kinda like being updated regularly. You can also safely assume that databases sto
Eddy Vluggen: That means you need a UI. That probably means you'd need to write one, generate one, or use Access as your front-end. My thoughts were to restrict non-techie access completely from the database. They can enter data via spreadsheets which we would subsequently import. And to get data they can simply use the spreadsheets or pdfs that the reporting would generate. I found on the last project, having many people access the database at once resulted in many issues. Eddy Vluggen: The database doesn't care where the data comes from. A is a simple import-operation. B and C cannot be automated, someone will have to enter the data. D is missing. I actually merged D with C and forgot to change it to 3 sources after the edit. Agreed that the database doesn't care how it gets it's info. This was I suppose an extension to the previous point that if we hire people to do data entry, it would probably be best in my opinion to have those workers enter the data into spreadsheets that would be imported to the database rather than having them enter the data straight into it with forms. The less people clicking around in the db not knowing exactly what they are doing, the better IMO. Eddy Vluggen: Data doesn't do things, it just sits there. Databases are also usually not responsible for generating a report; you will probably need to create a report-template that gets filled dynamically, with a filter for each property. I'd put the database in SQL Server, link from Access to it, and exclaim it's "done". Access has a low learning-curve and everyone could click together the reports as they see fit. Excell could probably do the same. I guess my wording here was awkward. I know that data itself cannot do things. I meant that I would like to set up some process by which we can generate report blanks, and pull the data from the database to auto-populate the reports. I know Excel can do it via exports but then it becomes the issue of everyone wasting their time trying to make the reports look professional, consistent, and legible, not the end of the world mind you but it gets old quick when you have to generate reports continuously. As mentioned earlier I was completely unimpressed with the UI for Access's report builder but looking around I have not seen any alternatives that seem any better. My mindset is based more around the NoSQL movement so I would simply write a script to scrape the data off a document database and generate the report via HTML & CSS
-
On the last project I was on, we utilized MS Access and while I was for the most part able to get around in Access, I found Access performed very, very slowly for even simple tasks once the DB grew to a certain size, and I was not at all impressed with the Report builder as I found it was almost always easier to export the data to Excel to generate my reports. Fast forward a few months and I am about to start a new project and I have been given quite a bit of say in how we set up our database. I do have a vague idea of how I would implement it but I am very curious to see others opinions as well to make sure I have all bases covered. Here are the 'contraints' if you will for how the database will need to perform. 1. There will be a few technologically savvy employees (approx 4-5) on site and the remainder of the workforce will be fairly illiterate, technologically speaking (they will be able to surf the web, do data entry in spreadsheets, type documents, but not much else). All employees will need to be able to access the data from reports easily and quickly without having to memorize commands or know how to navigate a database etc. My fellow techies have experience mostly with MS Access, and a couple know SQL, I am more familiar with NoSQL type frameworks, but have a basic understanding of SQL and can get by in such a system. 2. Data will come from 4 sources. a.) Primarily, most data will come via spreadsheets. These spreadsheets will often be fairly complex with multiple tables, hidden columns and rows, formulas, reference cells, etc. b.) The next largest chunk of data will come from field reports hand written by the foreman on site. c.) The next largest chunk will come from simple verbal communication, or emails, word documents, or even typed letters. 3. Data will need to auto-generate reports. The more autonomous this can be made, the better. We might have as many as 10 different daily reports as well as hundreds of weekly, monthly, and yearly reports that we will have to generate. Ideally, this could be performed by non-techie users simply by providing date ranges or other simple constraints, and having templates scoop the required data from the database. 4. Data will need to be backed up often (at least once maybe more per day, to prevent data loss or erroneous operations, and preferably will not interfere with it's usage at all. It needs to scale well, be reliable and not be subject to data loss, or easily let data fall between the cracks. In terms of importance I believe it shou
Like Eddy I am prejudiced to SQL Server, partially because I have been working with it for years but mainly because it has the most support resources on the interweb. You are discussing multiple requirements, the primary on is to set up a database to store and manage your data, here I agree that you should get in a professional to design your data structure, it is by far the most critical component. Next is the user interface, you need to build at least 1 application, your non tech access to the data and processes. You also need to create and interface for the existing Excel sheets into the data. Lastly you need a reporting strategy, I would recommend SQL Server Reporting Services (SSRS) this can be set up as both server based or embedded in you application. I believe you are in the position of a power user just dipping into becoming a developer and your tool horizon is limited to Office products, you might want to expand it to include a proper development environment.
Never underestimate the power of human stupidity RAH
-
Eddy Vluggen: That means you need a UI. That probably means you'd need to write one, generate one, or use Access as your front-end. My thoughts were to restrict non-techie access completely from the database. They can enter data via spreadsheets which we would subsequently import. And to get data they can simply use the spreadsheets or pdfs that the reporting would generate. I found on the last project, having many people access the database at once resulted in many issues. Eddy Vluggen: The database doesn't care where the data comes from. A is a simple import-operation. B and C cannot be automated, someone will have to enter the data. D is missing. I actually merged D with C and forgot to change it to 3 sources after the edit. Agreed that the database doesn't care how it gets it's info. This was I suppose an extension to the previous point that if we hire people to do data entry, it would probably be best in my opinion to have those workers enter the data into spreadsheets that would be imported to the database rather than having them enter the data straight into it with forms. The less people clicking around in the db not knowing exactly what they are doing, the better IMO. Eddy Vluggen: Data doesn't do things, it just sits there. Databases are also usually not responsible for generating a report; you will probably need to create a report-template that gets filled dynamically, with a filter for each property. I'd put the database in SQL Server, link from Access to it, and exclaim it's "done". Access has a low learning-curve and everyone could click together the reports as they see fit. Excell could probably do the same. I guess my wording here was awkward. I know that data itself cannot do things. I meant that I would like to set up some process by which we can generate report blanks, and pull the data from the database to auto-populate the reports. I know Excel can do it via exports but then it becomes the issue of everyone wasting their time trying to make the reports look professional, consistent, and legible, not the end of the world mind you but it gets old quick when you have to generate reports continuously. As mentioned earlier I was completely unimpressed with the UI for Access's report builder but looking around I have not seen any alternatives that seem any better. My mindset is based more around the NoSQL movement so I would simply write a script to scrape the data off a document database and generate the report via HTML & CSS
Sea_Sharp wrote:
My thoughts were to restrict non-techie access completely from the database.
Ditto; the Access-frontend would link purely to readonly-views on their own version of the database; a simple restored backup of the production-database (on Sql Server). Then again, most people would not offer Access to an end-user and claim that it's the UI they should work with.
Sea_Sharp wrote:
I found on the last project, having many people access the database at once resulted in many issues.
Yes and no. Access does it's job well, but it was never meant to store a lot of data and be accessed by multiple users simultaneous. A database-server is meant for that job, and Acces isn't a server-application - "just" a desktop app.
Sea_Sharp wrote:
I guess my wording here was awkward.
Sorry for my tone/wording; it'll be equally (if not more) awkward (or hostile) at some points.
Sea_Sharp wrote:
I know Excel can do it via exports but then it becomes the issue of everyone wasting their time trying to make the reports look professional, consistent, and legible
..aaah, yes, users are resourceful.
Sea_Sharp wrote:
As mentioned earlier I was completely unimpressed with the UI for Access's report builder but looking around I have not seen any alternatives that seem any better.
The Query-Builder looks scary, but is a very powerful tool; combine that with the reporting, and you have a "simple" UI - although others will certainly disagree there. Combine that with the free runtime-version of Access, and you got a UI that will be hard for the end-user to "break".
Sea_Sharp wrote:
You should have seen how bad our MS Access db got corrupted just by having 2-3 people working on it simultaneously.
Yes, especially when they're mucking in the same tables. When moving to SQL Server, be sure to read up on locking and transactions - though the concept does exist in Access, it doesn't help to improve multi-user access. Locking is what keeps the data consistent in a multi-user db, but it also introduces a bottleneck.
Sea_Sharp wrote:
I mentioned earlier I think it would make a lot more sense to do it HTML and CSS or XML and XSLT for professi
