Re: experienced consultant wanted for MariaDB schema and query job

On 1/17/2019 8:05 PM, tgrom.automail_at_nuegia.net wrote:
> Hello, I am looking for someone with experience engineering database
> schemas in MariaDB and has done prior work with with INDEXing and
> KEYing to help consult for a project.
>
> I am working on implementing a in house product database for multiple
> brick-and-motor as well as online stores. I have already implemented
> most functionality, I am just in need of someone with extensive
> experience to sanity check and help consult on the proper schema that
> would be needed to store the data we need. Some of the data is listed
> in this picture URL.
>
> https://i.imgur.com/DjyKx0R.png
>
> Your work would be relatively short, perhaps a 2-4 hours. We just need
> some sanity checking. We can implement this ourselves.
>
> Our current database schema consists of 1 database per company, 2
> tables per company.
>
> table 1 'store' stores data that is specific to the sku but can be
> different across stores. such data like how much stock at each store,
> and the sales price at each store.
>
> CREATE TABLE `store` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `store_num` int(4) NOT NULL,
> `local_sku` varchar(64) DEFAULT NULL,
> `stock` int(11) DEFAULT NULL,
> `store_price` decimal(15,4) DEFAULT NULL,
> `c_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
> `m_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
> `sync_time` datetime DEFAULT NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `store_num` (`store_num`,`local_sku`)
> ) ENGINE=InnoDB AUTO_INCREMENT=24806 DEFAULT CHARSET=utf8
>
> the other table would hold data this is the same across the sku not
> different across the store. data like the description of the product,
> UPC, vendor, etc.
>
> This table is not fully implemented yet and is the part we would want
> your help with making sure it's done right to the best of our abilities
> before we start building a bunch of software atop of this.
>
> I would like to offload as much logic as possible to the database
> itself, using INDEXs, KEYs, and intelligent queries.
>

Why do you have a separate database for each store?

The first thing you should do before you call in a consultant is to normalize your database(s). If you're unfamiliar with normalization, Wikipedia (although I hate to direct people there) has a pretty good article on database normalization. Typically you will aim for around 3rd normal form.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================