List of advantages by designing table in normalized state.

Hi Experts,

I am in need of some help in order to convince manager to agree on designing a table in a normalized manner.

Attaching two versions of the form, the first one is a none normalized version, while the second is in normalize state.
Also attaching how table definitions would be according to each of those forms.

Basically would need a list of advantages vs. disadvantages we are to expect by choosing each path, in this case in particular.

Since users are used to enter data manually in sheets in a weekly bases (similar look to the denormalized version) they have preference on taking that route.

FYI-Table in question is PatientsMedications (posting test data).

Thanks
Untitled.png
Untitled1.png
Untitled2.png
Untitled3.png
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
1.  The major advantage of normalizing your data is it is easier to maintain.  With your example, what happens if someone needs meds every 2 hours, you don't have enough columns and if you need to add one, you have to totally rewrite your forms and reports.  With the normalized data set, you can have as many doses per day as you want, simply by adding another number in the sequence (rows to the table).

2.  It is easier to query the data in a nomalized table, for example, write a query for the denormalized dataset to count the number of missed doses, by medication in a given week.  Not very simple, but with a normalized dataset this is quite simple.

For me, these are the prime examples of why to normalize.  It can make the process of data entry a little more complicated, but the benefits far outweigh the disadvantages.
Jeffrey Dake Senior Director of TechnologyCommented:
Only benefit I have ever had to de-normalizing data is for query optimization and I have almost always regretted it.  It can make it a pain later down the road when information changes and you have to go update it in multiple tables (Ie a patient or a drug changes names).
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
You may like to source resources like https://www.itprotoday.com/sql-server/sql-design-why-you-need-database-normalization
It comes down to elimating duplication, standardization of data, can impact performance, ...
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

bfuchsAuthor Commented:
Hi Experts,

The main problem I have is that manager says,"if users are not getting to see the entire week at once, they will not do it (they will rather continue doing it manually)."

When I tried explaining some of the reasons mentioned here
for example, write a query for the denormalized dataset to count the number of missed doses, by medication in a given week.  Not very simple, but with a normalized dataset this is quite simple...
The answer was,"I know its more complicated however its still doable, while if we do it the other way, we may not get to use it at all..."

See attached (partially) what they are currently using to enter on paper.

Perhaps there is a way to accomplish both, having the tables designed in a normalized state and still be able to design a data entry form looking similar to
 this...?

Thanks,
Ben
Capture.PNG
Dung DinhDBA and Business Intelligence DeveloperCommented:
Advantages
- It's easy to maintain data such updating master data,....
- Avoid duplicated data
- Data is consistency
- Easy to integrate with other components in your application

Disadvantages
- Not easy to understand business
- Performance issue maybe. When you query data for a business requirement, you must join many tables.
- Require more effort on coding, depending on your approach (database first or code first)

In my point of view, you can do POC on both approaches and give the result to you managers. It seems he wanted to resolve the business rules quickly.
Jeffrey Dake Senior Director of TechnologyCommented:
My advice would be to take a step back. You are talking about normalizing your database, which you should absolutely do. However your boss is talking about the user interface and experience. Those two things shouldn’t dictate each other. You should figure out what the best experience is in your program for your users. Then figure out how to store the data normalized. That would be my recommendation to make you and your boss happy. It really should be your program that does the logic to store the data correct.y independent of how the data is entered or presented.
PortletPaulEE Topic AdvisorCommented:
I fully agree with Jeffery Drake (immediately above)

The front-end (your manager's view) can be very different to the back-end (your view)

Your manager should specify what he/she wants
You (and/or the technical team) should then decide on the best way to implement that

There is no reason why both cannot be satisfied.
Mark EdwardsChief Technology OfficerCommented:
The basic rule of thumb is that if it's an "OLTP" (Online Transaction Processing) app like a sales ordering process or a point-of-sale app, where you are picking a lot of pieces from dropdowns on a screen and assembling them into a unit of business, then it should be normalized for a lot of the reasons mentioned above.  If it's for Online Analytical Processing reporting purposes (OLAP), then a de-normalized "Datamart" structure works best as a lot of business stuff like invoices, etc. are made from a lot of normalized data into a set group of data - and should remain that way.  If I had a dollar for every newbie who wanted to "normalize" a datamart because he learned from an Access book that normalizing everything was the way to create all databases, I'd be able to retire now....

Also be aware that a database is NOT a spreadsheet - you don't add columns - you add rows.  Regardless of which type of db you use, you should be able to add nothing but rows of data and NOT make any structure changes that would require changing your code or processing every month as you move through time from month-to-month and year-to-year and add/lose people & customers, etc.  If you have to add columns every month or change your database structure as the calendar goes by or the players change, then you are REALLY mucking it up big time.

Normalized data CAN BE PRESENTED to the user in a de-normalized view.  That's one of the main jobs of queries, forms and reports.  The "normalization " of data is primarily for behind-the-scenes benefits that the user should never see, much less be involved in (it's an "engineering" thing).  To them, everything should look the way it needs to look.  They SHOULD have all the data that they need to make a decision or see the whole unit of business data at pretty much a single glance.  It can be done.  I do it all the time!

I've got a sneaking suspicion that a spreadsheet designer is wanting to build a database (been through that dog fight many a time) .  It's a dangerous endeavor wrought with pitfalls and frustrations because Access and a database DOES NOT work like a spreadsheet - it is far superior for handling data if done correctly.  Now the only thing I use Excel for is to suck data into a database, and then spit it out into a handy, dandy report for the Excel kiddies.

If you're trying to convince a spreadsheet guru to make a database NOT like a spreadsheet - good luck.....
Dave BaldwinFixer of ProblemsCommented:
If the user data that is entered needs to be reviewed or audited later, then you should keep a copy in the original format, non-normalized.  You can copy it to a normalized form for other purposes.
John TsioumprisSoftware & Systems EngineerCommented:
Well i might be a bit off but the way i see it you have 2 options
1. Just let the manager do his/her thing...you have already made your points ...we all know normalization is the way to do things...etc...etc
But working in the denormalized state will have the benefit of avoiding a conflict  with the manager while having the advantage in a later time to say "i told you so"...this might bring other benefits...and so on
2. If you want to keep the normalized state i reckon you have to delve into the MsFlexGrid control to "emulate" the paper form while keeping the data "normalized"...maybe you will need an auxiliary table to hold the "structure" of MsFlexGrid.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark EdwardsChief Technology OfficerCommented:
p.s.  "Normalization" is something that should be utilized by those folks who know when and how to use it, and why.  Normalized data, when viewed in the raw, is hard to visualize (make sense of) and just scares people who don't know what it is because they can't understand their data.  Normalized data is "engineered" for assembling pieces of data that are located in only one place into blocks of business data units that make sense to the business people.  

If you are going to build a sales order, you select a customer from your normalized customer data tables.  The selected customer then gets all of the relevant customer data from the other customer data associated tables, including contact information, location and mailing/shipping addresses, special deals and preferences, etc.  You then select the products from your normalized product tables and based on the customer data, add any packaging customization, quantity preferences, etc., etc, etc.

However, if your dealing with processed data, like a bunch of invoices for certain customers done on certain dates (used by business analyst who like to create fancy charts and graphs), that data is already associated properly and fixed.  It would be "stupid" to try and normalize it (tear it to pieces) because all you accomplish by doing that is adding the extra step of having to re-assemble it back to the way it was before you can use it.

….just trying to give a 10K foot view of these kinds of decision processes....  Google "OLTP", "OLAP", "Data Normalization", and "DataMart" to get more ammunition for making your point.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The answer was,"I know its more complicated however its still doable, while if we do it the other way, we may not get to use it at all..."

See attached (partially) what they are currently using to enter on paper.

The thing is, it may not always be doable.

 One of the things that hasn't been mentioned is that Access has some internal constraints that cannot be broken.  For example 255 fields in any table or query output, or 2048 table IDs.  Another is table locks....you may bump into "out of resources" because so many locks are consumed.   You may run into concurrency issues as you add more users.   Performance wise, an app may fall over with more than five or six users.   We've seen that time and time again here on EE.   People fault Access, when in fact the fault is the design.

If you don't design the database with normalization in mind, you will run into these more than likely and you won't be able to do what you want.  You'll be stuck.   Full stop.  

Access was designed and written to work with a properly designed relational database, which means it's normalized.

Jim.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd agree with Jeffrey Drake - your manager is referring to the User Interface, and you're referring to the data structure. You can create a UI that will support your manager's needs, and still maintain a normalized structure - in fact, it's fairly normal to do exactly that, since users have no concept of normalized data, and humans often have a difficult time working with normalized data.
Dale FyeOwner, Dev-Soln LLCCommented:
Because Access will not let you have subforms nested in a continuous form, the challenge you will have is the UI.

I frequently use non-normalized temporary table as the source for data on forms, and use the AfterUpdate event of controls in the form to actually write or update records in my normalized table, so that the values in the normalized table correspond to the values in the temporary table.

In your case, I would probably write a crosstab query (several actually) to identify all of the fields that need to go into your temp table.  As long as you are not attempting to display more than about 30-35 total fields in your temp table to account for all of the fields in your form.

What is the difference in Nurse1 and GivenBy1 in your non-normalized data structure, I don't see a field in your 1st attachment to account for both of those.
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
I agree with everyone that's saying that the DB design is not the same as UI, and it's important to normalize the DB.

The point I'll add is that you can create temp/work tables that match the denormalized structure you need for the UI.  You load them before presenting the form to the user, and process all the changes back into the real tables when they're done.  More work, sure. But it's the right way to do it.
PatHartmanCommented:
Ben, it is your normalized form that needs work.  As the others have said, the user is not involved in database design decisions, ever!  Would your builder let you redesign a part of your house to be "pretty" but unsafe?  Schema design is your job, not his.

For starters, it looks like the big list part of the form is not properly filtered.  It should be showing only the data related to the box on the lower left just as it does on the non-normalized form.  Also, if the user really wants to see 7 days across, you can actually do that very easily by using 7 subforms.  One for each day.  Here's a picture of a subform I built to compare multiple properties.  It's the same concept.
5AccrossSubforms.JPG
This form was tedious to build because the list on the left is on the main form and I had to futz with the sizing until I could get everything to line up since you can't do it in design view.  Access forms are not WYSIWYG.
5AccrossSubformsDesign.JPG
bfuchsAuthor Commented:
Hi Experts,

First i want to thank all for your valuable input.

I'm not in the office today, would have to leave for next week for discussing with manager, bring up your points and finalize it.

One thing I see from all your responses that basically everyone here agrees there is a way to accomplish both, the normalized table structure, and the design on the form like the manager wants, so I guess the answer on this question is pretty clear, and will likely have to open another thread focusing on ideas how to accomplish that form with normalized data structure.

What is the difference in Nurse1 and GivenBy1 in your non-normalized data structure, I don't see a field in your 1st attachment to account for both of those.
That is a hidden field, just to capture the nurse who entered the data.

Also, if the user really wants to see 7 days across, you can actually do that very easily by using 7 subforms.  One for each day.
Agree, and in fact I have used that trick in some occasions, however here if you realized there must be a record for each med multiplied by 7, if only Access continuous form would support subforms... that would be the easiest way out here.
See attached none normalized form in design view.

@Dale, afraid to have all actual data manipulation done by code, this may lead to other issues, besides of nullifying the biggest advantage of using Access as designer tool.

@Jim, Are your worries also relevant if this table will reside in SQL Server?

@John, Never dealt with MsFlexGrid control before, is that something easy to apply in Access forms, does it require heavy coding?

@Mark,
If you're trying to convince a spreadsheet guru to make a database NOT like a spreadsheet - good luck.....
Luckily not, there are just manager and users, and therefore if I manage to design the form the way they proposed, I get the green light...

Have a great weekend!

Thanks,
Ben
Untitled.png
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<@Jim, Are your worries also relevant if this table will reside in SQL Server?>>

  Not as much, but even SQL will struggle with a non-normalized database.  Keep in mind though that your still using Access as a FE, so the 255 column limit still applies.

Jim.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Never dealt with MsFlexGrid control before, is that something easy to apply in Access forms, does it require heavy coding?
I'd avoid this if you can. MsFlexGrid is an ActiveX control, meaning it won't work on many platforms. Plus, it quite old, and is not in current maintenance.
John TsioumprisSoftware & Systems EngineerCommented:
MsflexGrid along with other old ActiveX work just fine even in the newest versions (2016,W10 x64) so i reckon its quite safe to invest some time in getting a result that is probably impossible to implement any other way....my advise..before you say its too hard spend a day or two to explore it.
As a matter a fact in W10 the only thing that doesn't work well is heavy subclassing and strange API calls that add extra "abilities" to normal controls ...and in a few rare cases.
PatHartmanCommented:
Ben,
Please refer back to your older posts on this topic.  I suggested an application I call "Bound denormalized forms".  i can't post the database because it is too large.  If you want a copy, send your email address via PM.  Here's a pdf that describes the technique.  it is all done with queries.  The example uses 12 months.  You would use 7 days so your "master" query would be less complex.  Each "cell" requires only a couple of lines of code to ensure that normalized data can be saved.
Here's a picture of the form.  Each month is created by a separate query and the query the form is bound to.
BoundDenormalizedForm.JPGBound-Denormalized-Forms.pdf
Dale FyeOwner, Dev-Soln LLCCommented:
John,

Can you confirm that the MSFlexGrid control works in 64bit OFFICE?  I know it works in 32bit Office, on a 64 bit Operating System, but I don't believe it works in a 64Bit Office environment.

Dale
John TsioumprisSoftware & Systems EngineerCommented:
@Dale i never worked with 64bit...64bit office was more a marketing trick than a necessity
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
MsflexGrid along with other old ActiveX work just fine even in the newest versions (2016,W10 x64) so i reckon its quite safe to invest some time in getting a result that is probably impossible to implement any other way....my advise..before you say its too hard spend a day or two to explore it.
I didn't say anything about it being "hard". I said it's a bad idea to use ActiveX controls not designed to work in Access.

I did work with MSFlexgrid many years back. I found that it worked okay, but was not easy to deal with, poorly documented, and in general not the most robust control out there. I quickly found that if I wanted a grid control I'd have to use something else, and ended up using the Janus Gridex.

And what the author is asking is not "impossible" to do other ways. As others here have said, you can use temporary tables, or methods suggested by Pat to accomplish what Ben is after.
John TsioumprisSoftware & Systems EngineerCommented:
@Scott if Ms(H)FlexGrid along with a couple more ActiveX controls had better documentation i am pretty sure that the position of Access in Enterprise Application Development would be much higher....just do a search on the net to see how many people are still struggling to either implement the functionality of these controls or make them much friendlier to work with....
PatHartmanCommented:
I'm not sure why FlexGrid is under discussion.  I thought we were talking about normal vs not normal schemas.  The two solutions I proposed both use Access subforms and have no need for third party controls.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@pat

Because of the form presented that drove the question.

As with many things,  there is more than one way to approach this and solve it, and yours is not the only solution, which Scott summed up rather nicely in the closing of his last comment

Jim
PatHartmanCommented:
@Jim,
I wasn't suggesting that they were the only solutions, just that flexgrid wouldn't be an alternative because in itself, it doesn't solve the need to flatten the normalized design in and of itself.  There are always code solutions and temp tables etc.  If you are good enough, you can code your way out of any design flaw but using an ActiveX is just not wise unless there is no alternative.
John TsioumprisSoftware & Systems EngineerCommented:
@Pat just because you have implemented something  the way you implement it,doesn't mean that this is the only way...what if your client wanted to see more columnar data.
Anyway here is a screenshot of what i thought when i saw this question
FlexGrid.jpgIt would be interesting to see how the above could be implemented in another way...
PatHartmanCommented:
@John,
The example I posted is essentially this type of nunormalized grid (except my example is for months in a year rather than days in a week) and it does it with a properly normalized table with one row per expense per month.  So each record in the subform shows data from 12 rows joined into one.  The "grid" subform is updateable and when data is placed in a "cell", a new row is automatically inserted in the table.  The code in the BeforeUpdate event simply populates the foreign key.  The .pdf shows code samples.  If the grid control does that, then it will work but if it is bound to an unnormalized table or query then it isn't worth the aggravation of using a third party control.
John TsioumprisSoftware & Systems EngineerCommented:
@Pat how its going to be used is up to developer that will use it to implement it...The fact is that is far more versatile...it will auto-adjust to any future demand and it can be made to resemble the paper form.
PatHartmanCommented:
The question is - normalize the schema or not?  How does the Flexgrid answer that question?
John TsioumprisSoftware & Systems EngineerCommented:
I have written it above....in my 1st post...the data will remain normalized but they would look like the paper form...as i said probably it will require a table that will "hold" the "presentation"
PatHartmanCommented:
So your answer is to use an intermediate table? and then use code to identify and post back the changes, adds, and deletes?  I'm just trying to understand if there is something magical about the FlexGrid that makes it worth the problems of version and distribution.  It seems to me that except for better pretty-printing options, the FlexGrid works like an Access subform.  I don't even remember if it can be bound or not.  Can they be bound?
John TsioumprisSoftware & Systems EngineerCommented:
@Pat ...too many questions ..just take a look by yourself..
bfuchsAuthor Commented:
Wow,
Long time didn't have such an audience...-:)
Thanks again to all experts involved here!!
Ben
John TsioumprisSoftware & Systems EngineerCommented:
A challenging question always draws more attention...:)
PatHartmanCommented:
That would require me to locate a download site, download it, install it, read the documentation, test it and since I have no intention of using it, I am not going to take that time.  I thought I was asking an expert regarding its capabilities.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In short, a true grid control is more powerful than anything you can do with native Access forms/controls as they stand, and it's been one of the requested features to add to Access for a long time.   A close cousin would be an Excel spreadsheet.

 With a grid control, you have control of every cell, including embedding graphics, different cell sizes, etc. You just have a lot more options for presenting data.

 Anything along the lines of a calendaring/scheduling app would be far easier with a grid control than anything native in Access.

Jim.
PatHartmanCommented:
Thank's Jim.  I do know that much.  I haven't looked at a Grid control in 20 years.  My recollection was that they were not bound but needed lots of code to load and then more code to transfer the data back to the table.   John was implying that the Grid was some magic bullet for displaying normalized data in an unnormalized format and I was trying to get to the bottom of that.
Dale FyeOwner, Dev-Soln LLCCommented:
bfuchs,

I've put together a sample database with your general description, data is normalized, but dumped into a denormalized temporary table to display in the form (I've only done Sunday-Wed).  Each of the Caregiver and DoseAt fields calls a function in the AfterUpdate event of the control:

AfterUpdate: = UpdateNormal()

which parses the name of the control to determine which day of the week, dose number, and control Caregiver or DoseAt should be updated.
sample formI thought about trying to enable/disable controls based on the number of doses, but you cannot really do that in a continuous form.  If I were going to do this, I would probably add some error checking to the UpdateNormal to confirm that the users was not entering data in a dose# row that exceeds the number of doses for the day, and would also check to make sure that the user was not entering doses for a period outside the Start/End date of a particular perscription (because you are displaying a whole week at a time and the perscription might expire on a given date).  Again, because of the continuous form aspect of this, you cannot use conditional formatting of unbound controls without it affecting all of the other records.
Normalized-Data-denormalized-UI.accdb
bfuchsAuthor Commented:
@Dale,
Appreciate that.
Just leaving the office.
Will test it & let you know.
Thanks,
Ben
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.