1

I have two tables one is product which is a parent table with one primary key and i do have another child table of product, which is a product_details table. But the child table is linking with parent table(product) with logical data instead of foreign key,as we are doing this relationship with the help of java code in the coding side, instead of depending on the data base, which make it as tight couple.To avoid tight coupling between the tables we are storing the primary key value in the child table.

Please refer this image with data which is creating the logical relationship between Product and Product Details tables.

enter image description here

The Scripts are :

CREATE TABLE `tbl_product` (
  `product_id` varchar(200) NOT NULL,
  `product_details_id` varchar(200) DEFAULT NULL,
  `currency` varchar(20) DEFAULT NULL,
  `lead_time` varchar(20) DEFAULT NULL,
  `brand_id` varchar(20) DEFAULT NULL,
  `manufacturer_id` varchar(150) DEFAULT NULL,
  `category_id` varchar(200) DEFAULT NULL,
  `units` varchar(20) DEFAULT NULL,
  `transit_time` varchar(20) DEFAULT NULL,
  `delivery_terms` varchar(20) DEFAULT NULL,
  `payment_terms` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



 CREATE TABLE `tbl_product_details` (
  `product_details_id` varchar(200) NOT NULL,
  `product_id` varchar(200) DEFAULT NULL,
  `product_name` varchar(50) DEFAULT NULL,
  `landingPageImage` varchar(100) DEFAULT NULL,
  `product_description_brief` text CHARACTER SET latin1,
  `product_description_short` text CHARACTER SET latin1,
  `product_price_range` varchar(50) DEFAULT NULL,
  `product_discount_price` varchar(20) DEFAULT NULL,
  `production_Type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`product_details_id`),
  UNIQUE KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please suggest me, we are following this kind of relationship in my company, as the manager is saying it will give us flexible to us. Please suggest me the Pros and Cons of the design.I know that if we lose the data from the table, we can't know the relationship between the two tables.

  • 1
    Why do you have both a PRIMARY KEY and a UNIQUE key, both VARCHAR(200) in one of the tables?? – Rick James Aug 14 '16 at 4:33
5

If you want to use relational principles in database design then certainly not. In a relational design, each entity type in the business domain from the conceptual model is represented by an R-Table in the logical model. The R-Table is a table which, when following a specific discipline, can acquire the properties of a mathematical relational which enable the R-Table to be operated upon with logic statements of arbitrary complexity in an algebraic fashion with guaranteed results. Entity types at the conceptual level share a set of properties called attributes which describe them. One or more of these properties is defined to uniquely identify each entity of the class in order to tell them apart. These are mapped to the R-Table at the logical level as columns, and each Entity mapped to a row. Each row then represents a predicate, and when values for the columns are entered the predicate is instantiated to become a proposition.

A key component at the conceptual level are the business rules which define exactly what attribute values constitute a true proposition. These map to constraints in the logical model, which is a key benefit the relational model provides. When created, the constraints enable the DBMS, which can only manipulate symbols, to effectively keep the data entered consistent with the truth of the real world the data is meant to represent.

So in a nutshell, if you do not declare the constraints - such as a product price must be for one and only one product - to the DBMS, the DBMS will not be able to ensure the data is consistent with the real world. All bets are then off as to correct query results.

Relational design is not easy however. It requires detailed analysis of the business domain to be modeled, a full understanding of the business rules that define consistent data, and a careful mapping to the logical level of R-Tables. All too often what we see in practice is what I call file based design, where tables in the SQL DBMS are used to represent files whose content is based upon totally ad-hoc design considerations. A clue that the 2 tables in question - product and product details - were designed using file based design is in the name of the product details table. A file holds details about something. A Product Details is not an entity type whose class of entities share common properties. This is also evidenced by the fact that every one of those columns in that table are defined as NULL.

If you want pro's and con's of a file based design I am not the right person to elaborate, as those pro's and con's will be ad-hoc and totally based upon your specific circumstances - such as the technology and the work load in play.

A great primer on relational design and its power can be found in Fabian Pascal's Practical Database Foundation Series. Fabian lays out the basics for every day practitioners in easy to understand language. He covers everything I summerized here with clear explanations plus a whole lot more. I highly recommend it.

  • Thank you for the answer,will it be recommended to design such kind of data base without primary and foreign key constraints? – zameer Aug 12 '16 at 12:46
  • 2
    No it is not recommended because not having primary and foreign key constraints opens the database to having data inserted that is not consistent with the real word entities it represents. Inconsistent data will then lead to inaccurate query results. – Todd Everett Aug 12 '16 at 19:02
  • FOREIGN KEYs are not a requirement -- they are a convenience. Without them you need a INDEX and you need to either replace the "consistency checking" they provide, or ignore the problem. – Rick James Aug 14 '16 at 4:34
  • 3
    Indexing and Constraints have nothing to do with one another. Indexing is done at the physical level while constraints are declared at the logical level. An index may be used by a DBMS to implement a uniqueness constraint at the physical level but this is purely an implementation choice by the DBMS. Agreed that without declaring FK constraints database consistency must be checked by the application or ignored. Ignoring it will result in poor data quality. Checking it at the application level will still result in poor data quality in addition to less efficient checking. – Todd Everett Aug 14 '16 at 16:41
  • @ToddEverett: Fabian reads you too: dbdebunk.com/2017/01/this-week.html – onedaywhen Jan 10 '17 at 16:23

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.