Excel to SQL - Need Some Normalization Tips
-
I've been tasked with converting a very ugly spreadsheet into a database application. I've yet to decide on an interface but I do want to throw it into MySQL or SQL Server and then go from there.
The spreadsheet has a front end that reads and then displays data from a second worksheet called customer data. That data is stored a customer per row, including all of the notes up to 17 columns. So for example, a customer can have notes in Column G though Column V. If a customer has notes that go beyond column V, they would then go to Column W.
So what I've done in my early tests (testing with Access but not going to necessarily end up using Access) is to split the data from that single worksheet into two work sheets, one containing the customers, one containing the "Notes". I also inserted a column for ID on both sheets so that it would match the "Notes." I then imported into Access and set the relationship between the Customers and The notes via the ID. Sounds fine...
But the problem I am having is that each NOTE shows up in it's own field (Note 1, Note 2, Note 3, etc.) and it is kind of a messy way to enter the data going forward. The current spreadsheet allows a user to choose a customer at the top left and then all the notes from those columns are displayed in one huge white form and it is ugly. So you can see why we want to update this...
So, what I am looking for is the proper way to store these notes in a database and have it tie back to the matching customer. I've imported this into SQL and while it worked, I still have the problem of how to display the data from the columns. Management stills wants one large field that displays the notes, most likely with a vertical scroll bar to through through them.
Any hints?
-
I'd recommend MariaDB or PostgreSQL. Only two relational database servers I would choose without very specific needs to the contrary.
Definitely not SQL Server unless absolutely no choice for obvious long term licensing reasons.
-
@scottalanmiller said in Excel to SQL - Need Some Normalization Tips:
I'd recommend MariaDB or PostgreSQL. Only two relational database servers I would choose without very specific needs to the contrary.
Definitely not SQL Server unless absolutely no choice for obvious long term licensing reasons.
SQL Express works fine for most things
-
@scottalanmiller said in Excel to SQL - Need Some Normalization Tips:
I'd recommend MariaDB or PostgreSQL. Only two relational database servers I would choose without very specific needs to the contrary.
Definitely not SQL Server unless absolutely no choice for obvious long term licensing reasons.
Checking them out now.
-
@JaredBusch said in Excel to SQL - Need Some Normalization Tips:
@scottalanmiller said in Excel to SQL - Need Some Normalization Tips:
I'd recommend MariaDB or PostgreSQL. Only two relational database servers I would choose without very specific needs to the contrary.
Definitely not SQL Server unless absolutely no choice for obvious long term licensing reasons.
SQL Express works fie for most things
Most, but the problem is potential technical debt. It's an awesome choice if you know you will have no additional Windows costs for it, it never creates a lock in situation and you never outgrow it. While those things can often be likely, they are really hard to guarantee. So many companies start with it then find themselves stuck in five years with something that they are surprised to have to pay quite a bit for.
Right now, yeah it will work great. But does it add enough benefit to offset the risk? I've seen a lot of companies pay big fees because they thought that they'd not outgrow it, or not what to host it.
-
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
@scottalanmiller said in Excel to SQL - Need Some Normalization Tips:
I'd recommend MariaDB or PostgreSQL. Only two relational database servers I would choose without very specific needs to the contrary.
Definitely not SQL Server unless absolutely no choice for obvious long term licensing reasons.
Checking them out now.
MariaDB is drop in for MySQL. But it's the non-Oracle one.
-
You are aware that Excel can connect to the database, right? So you could keep the Excel front end even when moving to a database.
Maybe you don't want to do that, but you should know that you can.
-
Is it just a list of customers then a list of notes associated with them? Honestly sounds perfect for something much simpler than a relational database. Unless there are other details that I am missing or don't know about.
-
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
So, what I am looking for is the proper way to store these notes in a database and have it tie back to the matching customer. I've imported this into SQL and while it worked, I still have the problem of how to display the data from the columns. Management stills wants one large field that displays the notes, most likely with a vertical scroll bar to through through them.
Any hints?
If, in the end, the question is purely about display options, then it all comes down to the tool that you want to use to do the display. Access, Excel, PHP, NodeJS, Ruby on Rails... anything will do this, all depends on what you want to use and how you want it to look.
Heck from the description you could fire up NodeBB and make each customer a thread and each note a post and do it that way
-
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
So, what I am looking for is the proper way to store these notes in a database and have it tie back to the matching customer. I've imported this into SQL and while it worked, I still have the problem of how to display the data from the columns. Management stills wants one large field that displays the notes, most likely with a vertical scroll bar to through through them.
Do not worry about how to store the data.
Start with what you need to end up with and how it will be entered.
Those are two different things.
But those two will form the basis for what your data will have to be.So supposing you have these entry fields.
- ID
- Customer (multiple fields for info, name, account #, etc.)
- Note 1
- Note 2
- Note 3
You were posting about making multiple tables and getting close to 3rd normal form.
Works great, and is good design of course, but you also need to think about the purpose of this application.
But do you need to get that complicated? Any relational database can take all that in a single table with as many columns as needed.
-
@JaredBusch said in Excel to SQL - Need Some Normalization Tips:
Any relational database can take all that in a single table with as many columns as needed.
As will non-relational. Pretty much the sky is the limit here.
-
Seems like the display is really the tough part. Maybe start with a MOCK UP of the display and work backwards from there.
-
Yeah, the number one goal for management/end users is just something that is NOT EXCEL. Web-Based is what they want but it is limited on my (current) dev knowledge. Access would work for now for sure but not what I would choose. I used to really like LightSwitch but it is on the route of depreciation.
I think I am on the right track as far as forming the database but need something that will store and display these notes in correct database normalization.
-
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
Yeah, the number one goal for management/end users is just something that is NOT EXCEL. Web-Based is what they want but it is limited on my (current) dev knowledge. Access would work for now for sure but not what I would choose. I used to really like LightSwitch but it is on the route of depreciation.
I think I am on the right track as far as forming the database but need something that will store and display these notes in correct database normalization.
normalizing the notes into a separate table means they will look like this
- ID
- CustID (foreign key to Customer tbale)
- NoteNo (ie note 1, 2, 3, 4)
- NoteText
-
@JaredBusch said in Excel to SQL - Need Some Normalization Tips:
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
Yeah, the number one goal for management/end users is just something that is NOT EXCEL. Web-Based is what they want but it is limited on my (current) dev knowledge. Access would work for now for sure but not what I would choose. I used to really like LightSwitch but it is on the route of depreciation.
I think I am on the right track as far as forming the database but need something that will store and display these notes in correct database normalization.
normalizing the notes into a separate table means they will look like this
- ID
- CustID (foreign key to Customer tbale)
- NoteNo (ie note 1, 2, 3, 4)
- NoteText
I am visualizing that now...thanks...
-
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
@JaredBusch said in Excel to SQL - Need Some Normalization Tips:
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
Yeah, the number one goal for management/end users is just something that is NOT EXCEL. Web-Based is what they want but it is limited on my (current) dev knowledge. Access would work for now for sure but not what I would choose. I used to really like LightSwitch but it is on the route of depreciation.
I think I am on the right track as far as forming the database but need something that will store and display these notes in correct database normalization.
normalizing the notes into a separate table means they will look like this
- ID
- CustID (foreign key to Customer tbale)
- NoteNo (ie note 1, 2, 3, 4)
- NoteText
I am visualizing that now...thanks...
The only hard part is deciding where to loop or pivot the data. That depnds on how you end up designing things IMO. Sometimes SQL is better, sometimes your app is better to handle that.
SELECT c.CustID, c.CustInfo(muliple colmuns), n.NoteNo,n.NoteText FROM Customer AS c LEFT JOIN Notes AS n ON n.CustID = c.ID WHERE c.id = $INPUTFROMWEBFORMThis would dump it all out in 3 rows with the customer data repeated each time. Handle looping it out in your web app.
You could conversely write a loop or pivot in SQL to make it all come out on one line.
Or you could make 2 calls to the DB once for the customer data and once for the notes.
Really up to you. Whatever is easier for you.
-
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
Yeah, the number one goal for management/end users is just something that is NOT EXCEL. Web-Based is what they want but it is limited on my (current) dev knowledge. Access would work for now for sure but not what I would choose. I used to really like LightSwitch but it is on the route of depreciation.
I think I am on the right track as far as forming the database but need something that will store and display these notes in correct database normalization.
Look at the Meteor.js tutorials. I think you may find everything you need in the tutorial example alone.
-
-
@JaredBusch said in Excel to SQL - Need Some Normalization Tips:
@garak0410 said in Excel to SQL - Need Some Normalization Tips:
So, what I am looking for is the proper way to store these notes in a database and have it tie back to the matching customer. I've imported this into SQL and while it worked, I still have the problem of how to display the data from the columns. Management stills wants one large field that displays the notes, most likely with a vertical scroll bar to through through them.
Do not worry about how to store the data.
Start with what you need to end up with and how it will be entered.
Those are two different things.
But those two will form the basis for what your data will have to be.So supposing you have these entry fields.
- ID
- Customer (multiple fields for info, name, account #, etc.)
- Note 1
- Note 2
- Note 3
You were posting about making multiple tables and getting close to 3rd normal form.
Works great, and is good design of course, but you also need to think about the purpose of this application.
But do you need to get that complicated? Any relational database can take all that in a single table with as many columns as needed.
The purpose is to see what special requirements there are for certain customers. We currently have 145 regular customers who have special needs when we design a building for them. We want to be able to display the customers in a cleaner format and also make it easy for a designated person to update as needed.
And I am, for now, going to use Access because I need something quick and within my current skill level. And yes, when my skills expand, I do plan on redesigning this. The main problem I am having is displaying the notes. Each single note per customer is it's own field. For example, look at this wizard:
In creating a form,. it's wants me to select each note field and ends up looking like this:
This isn't want management wants and it will also cause problems for customers who need more than 16 "notes." They want to see the customer name, address, contact number and all notes associated with it. It would be nice to see all notes in a scrollable window. Not sure how easy that would be with Access but I'm thinking it would be. This is where I may be confused if I need normalization or not.
