6 Replies - 167 Views - Last Post: 01 November 2018 - 08:28 PM

#1 Viperion   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-November 18

Noob question regarding databases and frontends

Posted 01 November 2018 - 06:49 PM

Hello, and thank you in advance for reading this noob message. This is seriously just a long-term quest for investing my time in something useful, since I'm a bone-marrow-transplant patient (I have/had leukemia) and can't do much outdoors. I'm pretty much confined to my house and my business locale.

So, the story goes like this:

Before being diagnosed I started a small craft-beer business, and I also have a background in IT since I worked for 5 years in the information security department at a big multinational corporation (US-based) I don't want to give any further details about.

I have basic understanding (and sometimes a bit above "basic") of some things such as HTML5, CSS3, Javascript, Databases, how does a server work, what is an OS, which architectures are certain pieces of software built in and so on...

So, I have this simple open-source POS system for running my shop, and it uses a MySQL database. I also have been keeping an online catalogue which is just a Google Sheets doc that I MANUALLY modify every single day with the transactions we have every 8-hour working day. This is useful to me since people over social media always ask for a catalogue they can check before visiting us.

It occurred to me that I could invest my time in something less monotone and mechanic than manually subtracting product in a spreadsheet, and maybe I could gain some skills myself.

My questions are:

What do I need to look for /learn in order to make a database-driven online catalogue out of my already existing MySQL DB?

I know that I have to host this DB in some service so my web-catalogue and my in-site machine can both take the info from it. What I need to know is how to make both, the local POS system and the web catalogue feed from the same DB.

I don’t want to create online purchases from a site (e-commerce), I just need a way to exhibit the product details of the beers with a picture of them, but I do need the catalogue to show when we are out of stock in any of our products, therefore I need both to feed from the same DB.

And of course, I would like to learn how to create a front end for this DB, which would be my catalogue. I know HTML5, CSS3 and a bit of Javascript already, but I don’t know how to link the DB to any sort of front end, so any advice on what should I learn first and second (and last) would be greatly appreciated.

Well, if anyone makes it to the end of this message, please know that I totally understand if you do not reply, after all, everyone else here knows what the hell they are doing, and I am pretty aware of what time is worth. So, thank you for having read every single bit of my message.

Is This A Good Question/Topic? 0
  • +

Replies To: Noob question regarding databases and frontends

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: Noob question regarding databases and frontends

Posted 01 November 2018 - 07:29 PM

I'm glad to hear about the transplant. Hopefully that'll get you on your feet.

If your point of sale DB is local to a machine, but has client information on it, I would highly recommend _NOT_ attaching that to the internet. As a security guy you would be aware of the dangers of data and encryption/security. Not knowing the POS setup you may deal with a slight bit of drudgery keeping it air-gapped so to speak. Export to a CSV your POS inventory.. log into your web host and import it. Sure there's ways to make that a little less tedious but a whole heck of a lot better than say updating a google spreadsheet.

Web design isn't too bad. You would need your front end (html, css, jquery/js), and then a server side language to get data from your database (php, perl, python, ruby, ASP.NET, etc). Most web hosting sites give you at least one or two databases for any given plan.
Was This Post Helpful? 0
  • +
  • -

#3 Viperion   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-November 18

Re: Noob question regarding databases and frontends

Posted 01 November 2018 - 07:44 PM

View Postmodi123_1, on 01 November 2018 - 07:29 PM, said:

I'm glad to hear about the transplant. Hopefully that'll get you on your feet.

If your point of sale DB is local to a machine, but has client information on it, I would highly recommend _NOT_ attaching that to the internet. As a security guy you would be aware of the dangers of data and encryption/security. Not knowing the POS setup you may deal with a slight bit of drudgery keeping it air-gapped so to speak. Export to a CSV your POS inventory.. log into your web host and import it. Sure there's ways to make that a little less tedious but a whole heck of a lot better than say updating a google spreadsheet.

Web design isn't too bad. You would need your front end (html, css, jquery/js), and then a server side language to get data from your database (php, perl, python, ruby, ASP.NET, etc). Most web hosting sites give you at least one or two databases for any given plan.



Thank you so much for taking the time to read the whole thing, and for providing insight.

I don't have any relevant client data in the DB other than the client's name (No card numbers, no addresses). The names are linked to a unique ID in a way that, if we ever turn the shop into a restaurant/bar they can book a table with their names, but that's all.
I was thinking there must be some sort of way of encrypting the sensitive tables anyway (better safe than sorry). As for the catalogue, I only need a way of disabling the products that run out of stock from the standard view of it, and I was thinking how to make that work provided both the POS system and the web catalogue would feed from the same DB.

Another thing I thought about was some way of replicating the useful tables (The ones handling stock) in a web service in a daily basis so the clients can actually see what’s on stock. I only need to provide a graphic catalogue so the social media can attract potential clients directly to the “eye candy”, you know.

Anyway… I really don’t know where to begin, I think I will start to get my hands into some PhP courses (So I can link the DB to some front end) and of course… getting to know the DB structure (data modeling and the relevant tables for my purpose) would be a good start, I guess.

I am open to any suggestions.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: Noob question regarding databases and frontends

Posted 01 November 2018 - 07:58 PM

Certainly all of that is possible. Plenty of ways to encrypt sensitive information, have limited read only accounts pulling data, etc. All sort of second-tier DB issues.

The theory would be when your web page loads it makes a SQL SELECT call to your database of product and pulls it back. This could be as blunt as just showing what's in the table, or part of a search like amazon. You can have your SELECt statement exclude things with an inventory of 0, or have your page dispaly "Not available, check back later" when it is displaying the data.

Yup.. you could think about a web service and all that jazz, but maybe cross that bridge after you get the website/database interaction thing down first.

http://archive.oreil.../phpsql108.html
Was This Post Helpful? 0
  • +
  • -

#5 Viperion   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-November 18

Re: Noob question regarding databases and frontends

Posted 01 November 2018 - 08:09 PM

View Postmodi123_1, on 01 November 2018 - 07:58 PM, said:

Certainly all of that is possible. Plenty of ways to encrypt sensitive information, have limited read only accounts pulling data, etc. All sort of second-tier DB issues.

The theory would be when your web page loads it makes a SQL SELECT call to your database of product and pulls it back. This could be as blunt as just showing what's in the table, or part of a search like amazon. You can have your SELECt statement exclude things with an inventory of 0, or have your page dispaly "Not available, check back later" when it is displaying the data.

Yup.. you could think about a web service and all that jazz, but maybe cross that bridge after you get the website/database interaction thing down first.

http://archive.oreil.../phpsql108.html


Thanks once again, you've been super helpful. Is there any host you would recommend? Also, is there any good tutorials you know of? Or should I just start with any basic PHP one?

I am so grateful for the help, seriously.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14845
  • View blog
  • Posts: 59,246
  • Joined: 12-June 08

Re: Noob question regarding databases and frontends

Posted 01 November 2018 - 08:26 PM

Check out the tutorials here.. I am certain there's pluralsight/MS MVA classes if you want the whole 'talkie' bit.. obviously books may give you a better structured learning route.

Hosting? That could be anyone.. godaddy, 1and1, aw1hs, a little orange, dream host, etc.

You can also install a locally ran web server package if you want to just mess around before investing money. XAMPP, WAMP, LAMP, MAMP depending on your OS. Functions like a webserver, but restricted to local.
Was This Post Helpful? 0
  • +
  • -

#7 Viperion   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 01-November 18

Re: Noob question regarding databases and frontends

Posted 01 November 2018 - 08:28 PM

Awesome! Thank you so much!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1