Database design for web shop

[ergospherical]

I'm making an online shop and want to retrieve the inventory from a database to display on the website. I've done some database work with php and phpmyadmin before, except the database was already set up when I took over that project - and I don't remember much/at all about the project structure.

What database technology would be best for the task? I want to be able to add/remove products (product details and product images) easily as they will change very frequently, so remote access to the database is important. Is there a nice online system I can use - similar to what FireBase does for mobile apps)?.

 

[jack action]

I have a server that I access remotely and, thus, the MariaDB database on it is remotely accessible as well. Of course, using a program like phpmyadmin helps greatly. I personally use Webmin (which is for accessing the entire server but has a module for facilitating database access).

But I could write a program that would run on my computer and access the MariaDB database of my server if I wanted to. This is true for basically any database program that exists right now. So you really need to specify what you mean by "remote access is important". "Online database" just means you are accessing someone else's server.

What database technology would be best for the task? I want to be able to add/remove products (product details and product images) easily as they will change very frequently,

Without more specific criteria, probably most of the databases you can find here will do the job.

 

[jedishrfu]

I'm making an online shop and want to retrieve the inventory from a database to display on the website. I've done some database work with php and phpmyadmin before, except the database was already set up when I took over that project - and I don't remember much/at all about the project structure.

What database technology would be best for the task? I want to be able to add/remove products (product details and product images) easily as they will change very frequently, so remote access to the database is important. Is there a nice online system I can use - similar to what FireBase does for mobile apps)?.

You could consider any sql database. Sql gives you a lot of power the extract what you need and to organize it any way you want along with database update Atomicity.

I’ve used the H2 Database for several projects. It’s simple to setup and use just add water. Its a single java jar file meaning you need Java runtime installed. PHP should have odbc or jdbc drivers to access whatever database you choose.

www.h2database.com

 

[DrJohn]

Use an existing free shopping cart! Don't try to reinvent the wheel!

Properly tested, with lots of ideas and options you probably would not think of, lots of free and paid add-ons to do extra things. Checks and security, access to payment gateways, etc.

I've built several shopping carts, as well as online databases for simpler uses (house rental companies, membership accounts) and yet I still always recommend using a tried and tested free shopping cart. The one I use with my own clients has 100+ tables in it, and yet when I was thinking of designing one myself, I think only had about ten tables in my design. So the chances of covering as many options as existing products have is a bit remote.

This forum doesn't like links to sites that may sell you something, so you will have to do your own searching, unfortunately. Although a shopping cart with open in its name is quite good. (Moderators - if you don't like the hint in that last sentance, let me know and I will delete the hint.)

 

[pbuk]

What database technology would be best for the task? I want to be able to add/remove products (product details and product images) easily as they will change very frequently, so remote access to the database is important. Is there a nice online system I can use - similar to what FireBase does for mobile apps)?.

There are two "standard" choices, PostgreSQL (https://www.postgresql.org/) and MariaDB (https://mariadb.org/)/ MySQL (https://www.mysql.com/) /MariaDB. (Yes that is three, but MariaDB and MySQL are effectively interchangable).

PostgreSQL used to be considered more robust than MySQL, but as long as you use the (now default) InnoDB engine for your tables this is not something to worry about greatly.

There are web applications to interface with each of them (phpMyAdmin you have already used for MySQL, pgAdmin is similar for pg).

Bar far the best way to get up and running is to use a shared web hosting service. These come with everything already installed and maintained: there are many providers e.g. https://www.namecheap.com/hosting/shared/.

PHP is fine for writing this sort of thing, but don't just hack something together, use a framework: Laravel is probably the best option currently; unless you are familiar with designing, implementing and maintaining systems with a database layer I suggest you use the Eloquent ORM.

Other alternatives to PHP/Laravel/Eloquent include Python/Flask/SQLAlchemy and NodeJS/express/sequelize.

Bear in mind that for a decent front end experience almost all of the development time will be in Javascript - again you will want to use a framework, either React (which everybody uses) or Vue (which I like).

Oh, I assume this is a "fun/self-education" project? If you just want an online shop, use Magento/Zen Cart/WOO Commerce/etc.

 

[ergospherical]

Thanks for the replies - will spend some time looking over the suggestions.

Oh, I assume this is a "fun/self-education" project? If you just want an online shop, use Magento/Zen Cart/WOO Commerce/etc.

The plan is for a fully-fledged online shop to sell actual stuff. I want to keep costs as low as possible by building most of it myself, but am unsure about where to draw the line between cost vs time-constraints/security/functionality of the final product.

The static parts of the website are already written. My plan now is to have the inventory on a database, then dynamically load in “cards” for each product to the page with php. There would also be a basket & order feature. I haven’t decided whether to do transactions via PayPal or in-person.

I considered using an online shop tool (something like Shopify, which I worked with at a previous summer job), but don’t want to pay & want to use the website I’ve already written.

Am finding these suggestions very valuable! The most difficult thing is choosing a path to take…

 

[DrJohn]

Get opencart, it's free. or wordpress with woocart. Also free.
Both use PHP and MySQL / MariaDB

You would spend many months trying to create your shop and still get hacked to shreds. As for building your own access to a payment getway to process credit cards, which you will need, forget that as well. As for buyers entering card details and sending them to you, again hackers would attack instantly. If they can hack professionally developed systems, they can hack your system and get a copy of purchase orders with card details just by adding a few extra lines of code immediately before the code that sends details to you, to send themselves a copy as well.

You really are trying to reinvent the wheel.

 

[ergospherical]

Thanks. Have downloaded OpenCart and will play around with that. Am currently hosting the site locally (using MAMP), and am about to setup my database with phpMyAdmin.

Will it be easy to move all of this across to a remote server (for hosting) when it's programmed?

 

[DrJohn]

I've never tried to set it up locally then move it. Some links may be hard coded by accident to your local server. I did try a few tests locally to get the idea and methods to use, but only with a few new fake products.

I just go to the host, look for their quick instant install under Softaculous, in the cPanel, and with a few clicks it gets installed and make sure it uses https://. Then I add a simple index.html saying under construction, which will have a higher priority than the index.php of the cart. Next look for a nice free theme - the best ones cost, and can take quite some time to learn fully as I discovered when a client chose a very good but very complex theme. Then just create the categories, then the goods for sale in each category, set up the chosen card processing system and off it goes.

To go live, I delete the index.html file, and the index.php automatically becomes the one that gets loaded. Some clients have installed the shopping cart in a folder called cart so their earlier shop still runs while they transfer items to the new cart and find a theme they like. When the new shop is complete I then redirect calls to example.com to example.com/cart/ Just remember to redirect everything - http://, https://, www.example.com and example.com to the new location.

Also, you can have the equivalent of static information pages by using the category pages to describe a range of products and their advantages. The category pages will also automatically show each product in its category, and a product can be in two or more categories at the same time. EG an aircraft radio can be in instruments, radios, and the manufacturer's category.

 

[ergospherical]

What's a good host? Looking at FastComet.

 

[pbuk]

The plan is for a fully-fledged online shop to sell actual stuff.

Then don't even think about writing it yourself.

I want to keep costs as low as possible by building most of it myself

The software should cost you nothing, the only costs should be web hosting at say £50 a year and transaction fees for the payment gateway at around 2%.

The static parts of the website are already written.

That's a bit like saying "I'm building an internal combustion engine, I've already designed the bits that don't move".

I haven’t decided whether to do transactions via PayPal or in-person.

What do you mean "in-person" - coming to your house with a bundle of cash? The only realistic option in the UK is to use a payment gateway like Stripe or PayPal.

 

[pbuk]

I considered using an online shop tool (something like Shopify, which I worked with at a previous summer job), but don’t want to pay & want to use the website I’ve already written.

A pay-as-you-go online service like Shopify is a very different thing to a self-hosted open source shopping cart.