We help IT Professionals succeed at work.

List of advantages by designing table in normalized state.

192 Views
Last Modified: 2019-03-26
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
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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 Technology
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2018

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, ...
CERTIFIED EXPERT

Author

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 Developer
CERTIFIED EXPERT

Commented:
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 Technology
CERTIFIED EXPERT

Commented:
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 Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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 Officer
CERTIFIED EXPERT

Commented:
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 Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
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 (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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 (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Armen Stein - Microsoft Access MVP since 2006President, J Street Technology
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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
CERTIFIED EXPERT

Author

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 (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<@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 (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@Dale i never worked with 64bit...64bit office was more a marketing trick than a necessity
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@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....
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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 (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
@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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
@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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@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...
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
@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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The question is - normalize the schema or not?  How does the Flexgrid answer that question?
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
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"
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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 Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
@Pat ...too many questions ..just take a look by yourself..
CERTIFIED EXPERT

Author

Commented:
Wow,
Long time didn't have such an audience...-:)
Thanks again to all experts involved here!!
Ben
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
A challenging question always draws more attention...:)
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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 (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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 LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
@Dale,
Appreciate that.
Just leaving the office.
Will test it & let you know.
Thanks,
Ben

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.