I am working on a project in which there will be two spaces for the customer and the shopkeeper. Multiple shopkeepers can insert data and can modify the products in their shop(they will have a unique id for their shop).The customer with their shop id-they have been assigned can see all the details about their shopkeeper and the available products in his shop.

I have made the database for the one shopkeeper's product details

  `id` int(11) NOT NULL auto_increment,
  `product_code` varchar(60) NOT NULL,
  `product_name` varchar(60) NOT NULL,
  `product_desc` tinytext NOT NULL,
  `product_img_name` varchar(60) NOT NULL,
  `qty` int(5) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `product_code` (`product_code`)

can somebody help me in the designing the database structure for my project any suggestions are welcomed for improving the efficiency?

  • Edit your title to describe the specifics of your question. – Basil Bourque Sep 27 '17 at 14:28
  • A "product code" is probably limited to much less than 60, correct? Make product_code the PRIMARY KEY and get rid of id. – Rick James Sep 27 '17 at 18:15

Foreign key

You forgot the foreign key field linking these records to the shop.

You need to add a column in this table here to store the primary key value of the shop who owns these products.

Eliminate redundant data

If there is any chance of needing the product info elsewhere, or any chance of having one shopkeeper track more than one quantity of that product, the the product fields of code, name, description, and image should be moved to a separate parent table named product while this table is renamed something like stock. You would add a foreign key to link each child stock record to its parent product record.

You gave yourself a clue about this when you prefixed the names of these columns with “product_”.


Other points…

It appears that you are wrestling with multitenancy, the technical term for having various shopkeepers as your customers where each sees only a subset of the data, each handled as if the database were just for them yet having to be commingled with other shopkeepers. You may want to search on that term to learn some techniques and guidance.

No need to use quotes on your particular column names. Indeed, doing so can change the way the column name is stored in some databases.

You might want to stick with standard SQL data types and avoid proprietary ones such as tinytext in case you ever decide to move up to another database such as Postgres.

Your table name should be singular rather than plural, product rather than products, shopkeeper rather than shopkeepers, per relational theory.

I do not know the specifics of MySQL, but robust databases handle Unicode, to store a wide range of possible cgaracters. So rather than specify Latin-1 as the character set, use something like UTF-8 if MySQL is capable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.