Zydeceltico
Registered User.
- Local time
- Today, 19:37
- Joined
- Dec 5, 2017
- Messages
- 843
Hi All,
I don't know how many times (over the past couple of years) I have written the next sentence (lol): I work for a steel manufacturing company in QC. I developed an inspection database in Access 2016 to collect our daily inspections and provide reporting on activities. The inspections take place on the floor via a 12" rugged tablet.
Well...I am happy to report that it has been a success. So much so that President of the company wants me to continue developing to include our other two plants and also add several more upper-management folks to the list of end users (mostly for reviewing reports).
Here's the thing, not everyone has MS Access installed on their computers and it is my understanding that the free viewer is no longer available and....and ....and
Which led me to looking at alternatives. It didn't take me long to come across the idea of a browser-based solution.
Fortunately, I am not unfamiliar with HTML, CSS and front end design although translating the Access tabbed forms I currently have will probably take some mental backflips. I also link the inspection forms to a library of part drawing files in pdf that can be recalled instantly. It's pretty slick if I do say so myself.
So, I'm trying to figure out what the best stack will be for me to undertake this new project.
Some of you may recall that the business rules here lead me to having an almost EAV type table design which I ended up not doing because the queries were going to be so cumbersome. I ended up with a hybrid model that works very, very well although I have the types of inspection spread across several tables that all share a master linking table.
Somewhere in the past couple of weeks I read quite a bit about JSON files and light bulbs started going off in my head that that might be a great way to capture inspection data that is sometimes minimal and sometimes many, many fields.
My few opening questions then are:
1) am I barking up the wrong tree thinking JSON datatype would help me achieve a more robust approach to the myriad type of inspections that need to be performed;
2) If so, what would that stack look like?;
3) If not, what other stack(s) should I be looking at?
I've been thinking MySQL, Python, and Angular (what am I missing?). Or MongoDB alternatively but a good bit of my DB is traditional RDBMS - although it appears that would be fairly straight forward to migrate into a new model in a robust environment.
I've attached a snip of my current table structure. There are several more look up tables but they're fairly extraneous to the current discussion although they would be needed. I think it is fairly obvious that I have some redundancy in the inspection table structure. That's the area I'm wondering if the JSON datatype would be beneficial and collapse all of those inspection tables down to one.
I'm looking forward to your thoughts.
Thanks,
Tim
I don't know how many times (over the past couple of years) I have written the next sentence (lol): I work for a steel manufacturing company in QC. I developed an inspection database in Access 2016 to collect our daily inspections and provide reporting on activities. The inspections take place on the floor via a 12" rugged tablet.
Well...I am happy to report that it has been a success. So much so that President of the company wants me to continue developing to include our other two plants and also add several more upper-management folks to the list of end users (mostly for reviewing reports).
Here's the thing, not everyone has MS Access installed on their computers and it is my understanding that the free viewer is no longer available and....and ....and
Which led me to looking at alternatives. It didn't take me long to come across the idea of a browser-based solution.
Fortunately, I am not unfamiliar with HTML, CSS and front end design although translating the Access tabbed forms I currently have will probably take some mental backflips. I also link the inspection forms to a library of part drawing files in pdf that can be recalled instantly. It's pretty slick if I do say so myself.
So, I'm trying to figure out what the best stack will be for me to undertake this new project.
Some of you may recall that the business rules here lead me to having an almost EAV type table design which I ended up not doing because the queries were going to be so cumbersome. I ended up with a hybrid model that works very, very well although I have the types of inspection spread across several tables that all share a master linking table.
Somewhere in the past couple of weeks I read quite a bit about JSON files and light bulbs started going off in my head that that might be a great way to capture inspection data that is sometimes minimal and sometimes many, many fields.
My few opening questions then are:
1) am I barking up the wrong tree thinking JSON datatype would help me achieve a more robust approach to the myriad type of inspections that need to be performed;
2) If so, what would that stack look like?;
3) If not, what other stack(s) should I be looking at?
I've been thinking MySQL, Python, and Angular (what am I missing?). Or MongoDB alternatively but a good bit of my DB is traditional RDBMS - although it appears that would be fairly straight forward to migrate into a new model in a robust environment.
I've attached a snip of my current table structure. There are several more look up tables but they're fairly extraneous to the current discussion although they would be needed. I think it is fairly obvious that I have some redundancy in the inspection table structure. That's the area I'm wondering if the JSON datatype would be beneficial and collapse all of those inspection tables down to one.
I'm looking forward to your thoughts.
Thanks,
Tim