Zydeceltico
Registered User.
- Local time
- Today, 16:31
- Joined
- Dec 5, 2017
- Messages
- 843
Hi All,
I'm designing a DB for the quality assurance department of a steel manufacturing plant. The primary data collection is from inspections of materials and products as they are delivered, rolled, welded, painted, fabricated, etc.
The challenge is that all of the various TYPES of inspections share some common fields - a few - but all inspections have far more non-common fields. So...when thinking about table structure, there is quite a bit of redundancy if I went with the traditional RDBMS approach to table structure. I will have a huge table with hundreds of fields defining various inspection criteria and the vast majority of the cells will be blank. I personally don't like that design. It doesn't feel normalized.
It was suggested to me that I look at an EAV design which I'll admit, I could conceptualize, but found too complicated to implement - primarily because 60% of the DB lends itself strongly to RDBMS.
My current workaround is that I have one "umbrella" table (tblInspectionEvent) that collects all of the shared data and a separate table for every TYPE of inspection (e.g., tblWelding, tblPainting, etc). It works a charm for data entry. There are two challenges though with this approach. The queries for reports are extremely complicated (at least for me) with joins and such and if the VP of Engineering wants a report on ALL TYPES of inspections that were made on any given job, given that each type of inspection has all of its own inspection criteria, it is a real bugger to aggregate all of that into a single report.
So I've been toying with the idea of utilizing the JSON datatype (which is not natively supported by Access) for a variety of reasons including moving the front end to a browser-based solution (for a variety of reasons that I won't go in to here). My primary curiosity about JSON datatype is that appears that it can be utilized more robustly at the point of data entry than a typical RDBMS style approach. We do a lot of spot check inspections where we do not do full-blown inspections and only collect a few pieces of data (maybe only one) versus a full blown piece inspection which may have twenty or more fields that have to be entered.
While I was exploring this idea, it was suggested to me on this forum by isladogs, that I look at this piece of software that he developed: JSON converter for Access
I've only briefly perused the manual but it looks very promising. One immediate question: how does the converter handle updates to a table? Simply adding more rows instead of deleting existing? I'm sure the info is either in the video or in the manual but I thought I would ask more immediately.
It looks very promising and worth a look for anybody else who may be considering the JSON datatype.
Thanks,
Tim
I'm designing a DB for the quality assurance department of a steel manufacturing plant. The primary data collection is from inspections of materials and products as they are delivered, rolled, welded, painted, fabricated, etc.
The challenge is that all of the various TYPES of inspections share some common fields - a few - but all inspections have far more non-common fields. So...when thinking about table structure, there is quite a bit of redundancy if I went with the traditional RDBMS approach to table structure. I will have a huge table with hundreds of fields defining various inspection criteria and the vast majority of the cells will be blank. I personally don't like that design. It doesn't feel normalized.
It was suggested to me that I look at an EAV design which I'll admit, I could conceptualize, but found too complicated to implement - primarily because 60% of the DB lends itself strongly to RDBMS.
My current workaround is that I have one "umbrella" table (tblInspectionEvent) that collects all of the shared data and a separate table for every TYPE of inspection (e.g., tblWelding, tblPainting, etc). It works a charm for data entry. There are two challenges though with this approach. The queries for reports are extremely complicated (at least for me) with joins and such and if the VP of Engineering wants a report on ALL TYPES of inspections that were made on any given job, given that each type of inspection has all of its own inspection criteria, it is a real bugger to aggregate all of that into a single report.
So I've been toying with the idea of utilizing the JSON datatype (which is not natively supported by Access) for a variety of reasons including moving the front end to a browser-based solution (for a variety of reasons that I won't go in to here). My primary curiosity about JSON datatype is that appears that it can be utilized more robustly at the point of data entry than a typical RDBMS style approach. We do a lot of spot check inspections where we do not do full-blown inspections and only collect a few pieces of data (maybe only one) versus a full blown piece inspection which may have twenty or more fields that have to be entered.
While I was exploring this idea, it was suggested to me on this forum by isladogs, that I look at this piece of software that he developed: JSON converter for Access
I've only briefly perused the manual but it looks very promising. One immediate question: how does the converter handle updates to a table? Simply adding more rows instead of deleting existing? I'm sure the info is either in the video or in the manual but I thought I would ask more immediately.
It looks very promising and worth a look for anybody else who may be considering the JSON datatype.
Thanks,
Tim