Practice
Need to create a MYSQL table of discounts, where each client has their own discount for their own product group
there's already a table with clients and a table with product groups
CREATE TABLE IF NOT EXISTS `goup_product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `group_name` varchar(255) NOT NULL, `block` int(11) NOT NULL DEFAULT '0', `vid` int(11) NOT NULL DEFAULT '0', `autor_name` varchar(60) NOT NULL DEFAULT ' ', `autor_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) TYPE = InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1
CREATE TABLE IF NOT EXISTS `client` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ' ', `Tel` varchar(255) NOT NULL DEFAULT ' ', `fax` varchar(255) NOT NULL DEFAULT ' ', `Manager` varchar(255) NOT NULL DEFAULT ' ', `Email` varchar(100) NOT NULL DEFAULT ' ', `autor_id` int(11) NOT NULL DEFAULT '0', `region` varchar(255) NOT NULL DEFAULT ' ', `autor_name` varchar(255) NOT NULL DEFAULT ' ', `Status` int(11) NOT NULL DEFAULT '0', `Block` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) TYPE = InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 ;
I'm creating a linked table
CREATE TABLE IF NOT EXISTS `percentages` (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
client_id INT(11) NOT NULL,
group_id INT(11) NOT NULL,
PRIMARY KEY (id),
INDEX client_id USING BTREE (client_id),
INDEX group_id USING BTREE (group_id),
CONSTRAINT orders_ibfk_1 FOREIGN KEY (client_id)
REFERENCES db.client (id) ON UPDATE CASCADE,
CONSTRAINT orders_ibfk_2 FOREIGN KEY (group_id)
REFERENCES db.goup_product (id) ON DELETE CASCADE
)
ENGINE = INNODB
AUTO_INCREMENT = 1
AVG_ROW_LENGTH = 4096
CHARACTER SET cp1251
COLLATE cp1251_general_ci;
I'm only moderately familiar with databases. Need to think this through. I need to create a discount table where each client has their own discount for their own product group.
There's already a table with clients and a table with product groups, I'm creating a linked table `percentages`, but how do I populate it?
For each client I need to insert, by ID, all the product groups with their IDs into the table.
Is it possible to do this with the `percentages` table given this structure?
Answer - Look... there are clients, there are products. Does each client have their own discount on a specific product?
Question - Yes, on a specific product group, and everyone has different discounts, both for clients and for product groups, and the number of groups is arbitrary.
How do I correctly build the discounts table?
Answer - this needs to be designed, it's not something you just throw together..... Give me the table structure by columns as they're written, i.e. the column names in the tables
I'll step away for a bit, be back soon. In the meantime, write it up.
CONSTRAINT orders_ibfk_1 FOREIGN KEY (client_id) REFERENCES db.client (id) ON UPDATE CASCADE, CONSTRAINT orders_ibfk_2 FOREIGN KEY (group_id) REFERENCES db.goup_product (id) ON DELETE CASCADE
this links already existing records on delete or update, but how do I create them??? in the new, third table with the discounts
let me give you the table structure schematically - I already wrote the full version above
CREATE TABLE IF NOT EXISTS `client` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT ' ',................
CREATE TABLE IF NOT EXISTS `goup_product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `group_name` varchar(255) NOT NULL,....................
but what's needed is
CREATE TABLE IF NOT EXISTS `percentages` ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, client_id INT(11) NOT NULL, group_id INT(11) NOT NULL, PRIMARY KEY (id), INDEX client_id USING BTREE (client_id), INDEX group_id USING BTREE (group_id), CONSTRAINT orders_ibfk_1 FOREIGN KEY (client_id) REFERENCES db.client (id) ON UPDATE CASCADE, CONSTRAINT orders_ibfk_2 FOREIGN KEY (group_id) REFERENCES db.goup_product (id) ON DELETE CASCADE ) ENGINE = INNODB AUTO_INCREMENT = 6 AVG_ROW_LENGTH = 4096 CHARACTER SET cp1251 COLLATE cp1251_general_ci;.............
Question
is the structure of the third table, the one with the discounts, designed correctly? and how do I populate it with client and group data? What do you think?
Answer
first of all you don't need a Primary key. you're already using a composite PRIMARY KEY - it's 3 IDs, client-group-product - how is that not a unique enough parameter?
so the discounts table will be without a PRIMARY KEY?
explain why it needs a PRIMARY KEY there?
Okay, let's say that's fine, but how do I populate it - if the two tables with clients and product groups already exist? A double loop feels dicey - with all sorts of complicated conditions - I'd rather not.
Answer You'll need to link the product and the client and set the discount no matter what... it's a many-to-many relationship - many products, many clients, you need to split it up and (ideally) build a 1-to-1 relationship - 1 product, 1 client, 1 discount ....
Either way you need to start from the fact that 1 client has (DIFFERENT) discounts on each product (or on a group of products)
Question How do you actually do that in practice? Or is it 1 client - 1 product group - 1 discount?
Answer What columns do you have (with meaningful names, not your own names? what data do they hold?)
by table - I do read your code, but it doesn't tell me much. I'm not interested in the attributes. this is the design stage, not implementation
populate the tables according to the field types. In short, build nested loops to create the discounts table for the clients and product groups that already exist, or come up with a query.
Comments