1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

G Sheets vs MySQL (and other CMS stuff)

Discussion in 'HTML, Graphics & Programming' started by Russinating, Sep 11, 2018.

  1. Russinating

    Capodecina

    Joined: Dec 27, 2005

    Posts: 14,735

    Location: Bristol

    So I think I know the answer, but basically what are the major differences between running a website's database (<2000 visits a month) off G Sheet rather than MySQL? I'm guessing G Sheets is slower when dealing with large volumes?

    The reason I ask is it'd be easier for our team to add and amend content via a G Sheet rather than a MySQL db.

    I realise I should be using another CMS, probably, that adds that usability above MySQL. And despite being 'ok' in PHP and writing websites in Notepad++, I've never touched Drupal/Joomla and the like and always get a bit lost when I look into them.

    Any advice appreciated.
     
  2. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    Isn't there a row limit on G sheets? Can't remember off the top of my head.

    I'd also have thought that linking a website to G Sheets as a dbase would take more work than just creating a basic login and database connection for MySQL to allow your team to update.

    I've been watching these PHP tutorials on Youtube and they're pretty good, this is the one where you make a login system https://www.youtube.com/watch?v=xb8aad4MRx8

    This seems to have some useful info on using G Sheets as a dbase, and was written in March https://codingislove.com/google-sheets-database/
     
  3. Russinating

    Capodecina

    Joined: Dec 27, 2005

    Posts: 14,735

    Location: Bristol

    I don't believe there's a row limit, and certainly not one that would be hit by our website. The benefit is we're fully Drive integrated and even a new starter could add a new row to a sheet (as opposed to SQL).

    I've already written another site that uses G Sheet API and it wasn't too bad (and now it's all written).

    The site would only need read access, and the sheet would basically be a database of projects to display. That blog doesn't mention anything about speed though which is my main concern.
     
  4. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    Seems the limit is 2 million per worksheet. You may not think you'll hit that but it'd be daft to limit yourself.

    If you've already written one using G sheets what the speed like on that? does it get similar usage to the new site?

    it's an interesting concept i'd not thought about so I've been doing some googling and came across this via Azuqua:

    When to use a Google Sheets Database?
    1. Simple relationships between the data. If you require complex relationships, it’s better to use a relational database system, such as PostgreSQL, MySQL or MS SQL.
    2. Read-only records, or if speed doesn’t matter. One of the reasons to use a traditional database is for quickness of transactions. As you are interacting with your Google Sheet database via an API, this is necessarily a longer and more costly operation.
     
  5. Russinating

    Capodecina

    Joined: Dec 27, 2005

    Posts: 14,735

    Location: Bristol

    Ta. And whilst I agree on the row limit, I very much doubt we'd ever, ever get to even 500. That would mean we've worked on 500 individual client projects, all of which are being displayed online which would never be the case.

    It doesn't get similar usage no, as it's for a targeted campaign, but the speed seems fine. The above helps, so I think it's certainly worth me looking at.
     
  6. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 8,861

    Location: Stoke area

    Ahh I get you, then you may find if it's likely to not go above 500 G sheets may be fine for it.

    I'd suggest knocking something up and getting a few people to test the hell out of it. It's supposed to handle simple joins ok so it really depends how you plan the tables out.

    Sounds interesting though so good luck with it :)
     
  7. Vince

    Soldato

    Joined: Oct 30, 2003

    Posts: 6,260

    Location: Essex

    If it was me id be tempted to do it in wordpress as its much much easier to get fast results in than Drupal/Joomla. Probably a naughty word around here but with managed wordpress hosting providers available (I use WPEngine) to deal with all the known downsides like the constant updating testing etc it seems like a good deal to me these days. Plus wordpress is much easier to install, navigate and skin/develop on than Drupal/Joomla imo. I remember having worked in wordpress for a few years I inherited a Joomla website and feel your pain as I remember hating having to work on that thing. It got better over time but I never really liked it. Was glad to get rid of it when I stopped doing web work on the side.

    I see your talking about adding a row to a sheet but why not add the row through the front end via input boxes where you can do some sensible input validation, much like I guess you would do on a spreadsheet? At least this way you don't have to touch the back end at all.