Re: experienced consultant wanted for MariaDB schema and query job

On Fri, 18 Jan 2019 19:35:41 +0100
Luuk <luuk_at_invalid.lan> wrote:

> On 18-1-2019 02:05, 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.
> >
>
>
> What does 'qoh_01' - 'qoh_20' do?
> It seems pretty unlogical to store 20 numbers in 1 table with these
> names...
>
> 'price_01' - 'price_03' : same question.
>
> 'disc_01' - 'disc_03': same question.
>

That's just a draft my supervisor gave me, It's not normalized. It's just a list of some of the variables. I'm not actually storing each qoh, price, and disc in it's own column per store number. It's just one column which has the store number, not qoh_1,2,3,4,5 Received on Sat Jan 19 2019 - 01:36:53 CET