October 31, 2016 at 11:54 am
Hi!
I have a litle problem with following: I have some users (U1, U2, U3, etc.) who handles products (aa, bb, cc, dd, ee, ff, gg, hh, etc.) in differents stores (St1, St2, St3, St4, etc.).
Every user in the list wants to know in which store which products user can handle cheaper.
How looks out the tables and how looks out the queries if user want get at least the following 3 things (one at the time):
1- Get a list of products user marked in the system ("own" list).
2- Get the "own" list of products which has cero price in each store.
3- Get a list of lowest prices (but greather than 0) and see how much (total sum) user save if user handles the same products on others stores. Exemple:
Pr. St4 St1 St3 St2
bb $23 $27 $26 $28
ee $14 $15 $15 $20
hh $36 $38 $40 $37
Sum $73 $80 $81 $85
Count products 3.
Pr. St2 St1 St3 St4
aa $32 $33 $38 $36
cc $21 $29 $27 $25
ff $13 $14 $17 $20
Sum $66 $76 $82 $81
Count products 3.
Supouse you are a user that wants to know how much cost every product (from "own" list) in differents stores. Every user wants to know (after update a prices of the products in all stores) in which store which products are cheaper. It's about where user can handle cheaper.
Thank you in advance.
October 31, 2016 at 12:26 pm
This sounds a lot like homework and we don't do homework in here.
We can guide you, but you need to give specifics.
Be sure to read the articles on my signature.
You might also find this articles handy (if I understood correctly)
November 1, 2016 at 7:30 am
Hi Luis, thank your for answer.
It's very sad you think my question is a school homework. I'm new in DB world and I set up a linux box at home and fixed a litle things there, now I and some friends try to handle in those stores there the products are cheaper, we have 4 big stores in our area and we are 5 families: It's a source of my question, believe it or not.
I can do all we need with php, but why do DB work? DB do that much faster, flexible and simple that I can do with php.
If you want to help us we'll be very greatfull, if you don't want to, thanks for your sincerity Mr. Luís.
November 1, 2016 at 7:40 am
N_w (11/1/2016)
Hi Luis, thank your for answer.It's very sad you think my question is a school homework. I'm new in DB world and I set up a linux box at home and fixed a litle things there, now I and some friends try to handle in those stores there the products are cheaper, we have 4 big stores in our area and we are 5 families: It's a source of my question, believe it or not. If you want to help us we'll be very greatfull, if you don't want to, thanks for your sincerity Mr. Luís.
I'm sorry, but the problem has the structure of homework problems, that's why I got that impression.
We still need to have sample data in a consumable format. We need DDL, insert statements for the sample data and expected results. All those things are explained on the articles in my signature. Right now, you've only shown expected results, but that's not enough as we need something to work with.
November 1, 2016 at 8:01 am
I see. I don't know how begin, but I test with this (don't work as we need it):
CREATE TABLE `users` (
`idu` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`stores` VARCHAR(50) NOT NULL,
`pwd` VARBINARY(72) NOT NULL,
PRIMARY KEY (`idu`))
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
CREATE TABLE `stores` (
`ids` INT NOT NULL AUTO_INCREMENT,
`nm` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ids`))
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
CREATE TABLE `products` (
`idp` INT(11) NOT NULL AUTO_INCREMENT,
`prod` VARCHAR(50) NOT NULL,
`st1` MEDIUMINT(9) NULL DEFAULT '0',
`st2` MEDIUMINT(9) NULL DEFAULT '0',
`st3` MEDIUMINT(9) NULL DEFAULT '0',
`st4` MEDIUMINT(9) NULL DEFAULT '0',
PRIMARY KEY (`idp`))
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (aa,14,20,13,17);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (bb,33,29,38,33);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (cc,19,20,00,21);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (dd,22,29,25,33);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (ee,30,00,35,29);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (ff,10,14,11,13);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (gg,00,00,00,00);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (hh,16,22,30,10);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (ii,23,34,34,26);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (jj,41,32,39,41);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (kk,25,29,26,19);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (ll,24,27,10,24);
INSERT INTO products (prod,st1,st2,st3,st4) VALUES (mm,29,41,37,36);
I supose it needs a relationship between tables, but I not sure which structure the tables store and products must have.
I hope you understand what I tried to do.
Thank you.
November 1, 2016 at 8:33 am
First of all, this code seems to be MySQL. This is a SQL Server site, which means that most of us work primarily with that product. I can only give solutions tested on SQL Server that might need some work to translate them to another RDBMS.
Second, your products table is not normalized. If you ever need to add another store, you'll have to change your table design instead of just adding data. Here's a start on normalization: http://www.sqlservercentral.com/articles/T-SQL/normalization/584/
The way you have your design, you can code easily for each store, but each store will need new code. E.g.
--Products that are cheaper on Store 1
SELECT *
FROM products
WHERE st1 < st2
AND st1 < st3
AND st1 < st4
AND st1 > 0;
--Products that are cheaper on Store 4
SELECT *
FROM products
WHERE st4 < st1
AND st4 < st2
AND st4 < st3
AND st4 > 0;
Obviously, you don't want to be changing the queries, you want to be able to parametrize them. To be able to do so, you need to change your design to something like this:
CREATE TABLE products (
idp INT NOT NULL IDENTITY,
prod VARCHAR(50) NOT NULL,
store INT NOT NULL,
price INT NOT NULL
)
ALTER TABLE products ADD CONSTRAINT FK_Products_Stores FOREIGN KEY REFERENCES stores(ids);
Again, the code is for SQL Server, and you might need to correct it.
I have no idea what the users table is for. I hope that the stores column is not designed for a delimited list of stores. That's definitely not the way to go. You need to create an additional table to establish the relationship.
CREATE TABLE users (
UserID INT NOT NULL IDENTITY,
UserName VARCHAR(50) NOT NULL,
pwd VARBINARY(72) NOT NULL,
PRIMARY KEY (UserID));
CREATE TABLE stores (
StoreID INT NOT NULL IDENTITY,
StoreName VARCHAR(50) NOT NULL,
PRIMARY KEY (StoreID));
CREATE TABLE users_stores(
UserID INT NOT NULL,
StoreID INT NOT NULL
);
ALTER TABLE users_stores ADD CONSTRAINT FK_Stores_Users FOREIGN KEY (UserID) REFERENCES users(UserID);
ALTER TABLE users_stores ADD CONSTRAINT FK_Users_Stores FOREIGN KEY (idStore) REFERENCES stores(StoreID);
I hope this might give you an idea on how to rethink your project. I strongly suggest that you avoid the bad practices because they'll only give you problems in the future.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply