How to setup database tables that will store tabular data?

Question

I'm going to have a website that will have millions of tables (literal HTML tables with data in them) so I will need to store that. I'm battling with issue of whether each table row should be stored as a separate record in the database or if I should just store the entire table as JSON encoded data (or similar format).

The reason I am questioning this is that I want to allow users to have access to change data in each of the HTML tables and I want them to be able to just change data in one table cell for example, without having to go through editing the entire table.

It just seems like if I store entire table as one value it could be messy to change just one cell as I'd probably have to just replace entire table value each time instead of doing search and replace. However, if I store each table row as a record, I will have billions of records which could get out of hand quickly as I want to keep old versions in storage as well (in another DB table).

If I store each row as separate record, it will also have to be super duper relational as HTML tables will have headings, sub headings, and so on.

I have done some calculations and these are rough numbers:

~912 total businesses ~800 average locations in US ~729,600 total tables ~72 million total table rows

Here's how table will work from UI perspective so you can understand how data will be handled and manipulated.

Answer 1

Don't store HTML in the database -- just store the raw data, that's what databases are for. Then you can use PHP to format it on the fly however you like, be it an HTML table on a web page, a JSON-encoded string for an API query, high-level reporting, or a CSV file export. You'll need a table for businesses:

CREATE TABLE business (
    id UNSIGNED INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address ...
    city ...
);

One for locations of a business:

CRATE TABLE location (
    id UNSIGNED INT NOT NULL PRIMARY KEY,
    business_id UNSIGNED INT NOT NULL REFERENCES business(id),
    name VARCHAR(255) NOT NULL,
    description ...
    KEY (business_id)
);

And one for items of a location:

CREATE TABLE item (
    id UNSIGNED INT NOT NULL PRIMARY KEY,
    location_id UNSIGNED INT NOT NULL REFERENCES location(id),
    type VARCHAR(255),
    name VARCHAR(255),
    size VARCHAR(255),
    discount UNSIGNED INT,
    KEY (location_id)
);

You'll likely want to tweak indexes and key uniqueness based on your requirements. I imagine you'll want a unique key on (location.business_id + location.name) and (item.location_id + item.name).