Excel to SQL - Need Some Normalization Tips

scottalanmiller

@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

JaredBusch

@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.

scottalanmiller

@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.

scottalanmiller

Seems like the display is really the tough part. Maybe start with a MOCK UP of the display and work backwards from there.

garak0410

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.

JaredBusch

@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

garak0410

@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...

JaredBusch

@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 = $INPUTFROMWEBFORM

This 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.

scottalanmiller

@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.

scottalanmiller

https://www.meteor.com/tutorials

garak0410

@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.