Mysql – Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key

database-designforeign keyMySQLprimary-key

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.

Best Answer

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.

Related Question