Sunday, June 18, 2017

This Week

1. Database Truth of the Week

"The RDM is a formal system. It has two parts.
  • The Deductive Subsystem: the formal language
  • The Interpretation Subsystem i.e., the application--of that language
Without an interpretation subsystem there is no possibility of applying the formal system and it remains an abstract game of symbols.
Semantics is about applying the RDM to some subject. In effect, what you do is restrict the power of the abstract formalism so that it is more closely aligned with your intended use. That means you are using constraints to limit the vocabulary to the subject matter (and making it finite and usually fairly small) and restricting the possible interpretations that can be used consistently with the resulting subset of the formalism." --David McGoveran

Sunday, June 11, 2017

What Meaning Means: Business Rules, Predicates, Integrity Constraints and Database Consistency

Note: After the initial posting I corrected some errors. Readers of the initial post early Sunday should re-read.

Whether we like it or not,
"All semantics that can be formalized in FOPL--including verbs such as ‘supplies’—formalize as constraints. We use constraints to reduce the number of permissible interpretations—meanings—of the purely abstract FOPL deductive system. Codd did bring integrity to the forefront by making it an explicit component of a formal data model, but that does not mean that all the semantics that can be captured formally, have been captured in the standard description of RDM, let alone supported in products. In 1979 Codd described a way to "capture" semantics using the relational formalism beyond the then current understanding. That formalism doesn't tell you how to discover semantics, but if you have them, he shows (at least to some degree) how to express those semantics relationally." --David McGoveran
Read it all. 




Thursday, June 1, 2017

Redundancy, Consistency, and Integrity: Derivable Data

My May post @All Analytics

Database redundancy can wreak havoc with interpretation of analytics results, but it also poses consistency risks that can affect the correctness of the results themselves. The risks are too underappreciated for effective prevention. Given industry practices, analysts who use databases they did not design, or designed without sufficient foundation knowledge, should be on the alert.


Read it all (and please comment there, not here). 



 

Saturday, May 20, 2017

This Week

1. Database Truth of the Week

"If the physical model does not preserve the properties of the logical model, then it is—by definition—incorrect. That is why the physical model must be derived from the logical model, not the other way around." --David McGoveran


2. What's Wrong With This Database Picture?

"If we step back and look at what RDBMS is, we’ll no doubt be able to conclude that, as its name suggests (i.e. Relational Database Management System), it is a system that specializes in managing the data in a relational fashion. Nothing more. Folks, it’s important to keep in mind that it manages the data, not the MEANING of the data! And if you really need a parallel, RDBMS is much more akin to a word processor than to an operating system. A word processor (such as the much maligned MS Word, or a much nicer WordPress, for example) specializes in managing words. It does not specialize in managing the meaning of the words ... So who is then responsible for managing the meaning of the words? It’s the author, who else? Why should we tolerate RDBMS opinions on our data? We’re the masters, RDBMS is the servant, it should shut up and serve. End of discussion." --Alex Bunardzic, Should Database Manage The Meaning? 

Sunday, May 7, 2017

This Week Special: The US Sinking Deeper Into the Dark Ages

"...having nothing in them of the feelings or principles of ’76 now look to a single and splendid government of an Aristocracy, founded on banking institutions and monied incorporations under the guise and cloak of their favored branches of manufactures commerce and navigation [add technology], riding and ruling over the plundered ploughman and beggared yeomanry." --Thomas Jefferson

The first Donald Trump who comes along is all it takes to dismantle 200 years of American vaunted democracy and whatever little civilization was there by executive order. The "indestructible" system of checks and balances is collapsing like a house of cards. 


A nation that substitutes job training--if anything--for education, religion for reason and unbounded greed for morality regresses to the dark ages and self destructs.

The government has been taken over by a bunch of sociopathic (some also psychopathic) multi-billionaires who, in cahoots with utterly corrupt politicians in their pocket, have been exploiting and robbing the public blind for decades. Now they are obsessed with taking away even the pittance of basic healthcare from the weakest segments of the public--while redistributing obscene wealth to themselves and spending millions in public funds on personal security and preparing refuges
in fear of those whom they oppress. Even academia is in on it. This is no longer a society, let alone a civilized one. Watch its demise.
"The American Health Care Act, which the House of Representatives passed Thursday afternoon, is a cruel bill, one that seems exquisitely designed to afflict the afflicted, comfort the comfortable, punish the sick, immiserate the poor, and move the United States—nearly alone among advanced countries without universal insurance—further away from a morally defensible health-care system."
--The Ultimate Reverse Robin Hood: If AHCA becomes law, average households will lose money and millionaires would get a windfall

"The AHCA, even by conservative think-tank calculations, will leave many low-income and sick people without insurance they can afford, and does so even as it makes health-care work better for healthy people. Brooks’s explanation, and his close association of morality and health, with the idea that “good lives” produce good health, is just a recasting of the prosperity gospel. What’s a religious philosophy mostly pioneered by wealthy televangelists and megachurches got to do with pre-existing conditions and Medicaid reform?"
--The American Health Care Act's Prosperity Gospel

"Conspicuously absent from President Trump’s celebration of Obamacare repeal passing the House was any mention of the people and constituencies it might benefit."
--Are Americans 'Sick and Tired of Winning' Yet?

"For the bottom 60 percent of the population — that is, households earning less than about $67,000 a year — full repeal of the ACA would end up meaning an increase in taxes due to the loss of ACA tax credits. (Under the Republican bill Congress is considering, the tax credits aren’t totally eliminated — but they’re no longer designed to give the most money to the poorest Americans, so it’s likely that at least some of the poorest Americans would see their taxes go up.)

But people in the top 1 percent of the income distribution — those with incomes of over about $430,000 — would see their taxes fall by an average of $25,000 a year.

And for the true elite in the top 0.1 percent — people like designated White House senior adviser Jared Kushner, Treasury Secretary Steve Mnuchin, Education Secretary Betsy DeVos, Commerce Secretary Wilbur Ross, and many major campaign donors — the tax cut is truly enormous. Households with incomes of more than $1.9 million would get an extra $165,000 a year in take-home pay under full repeal. That’s obviously more than enough money to make these hyper-elite families come out ahead regardless of what happens to health insurance markets."
--Repeal cuts taxes for millionaires, even if millions lose insurance

"Congress members and their staff, however, are so far still exempt from any changes."
--The Republicans’ amended health-care law could be devastating for four big groups of Americans

"Many experts believe the bill will result in millions losing their health insurance coverage. The CBO reported that the earlier draft of the bill would have led 24 million more Americans to find themselves uninsured by 2026. As written, the AHCA would also cut taxes for wealthy Americans and could weaken benefits for the 49% of Americans who receive health insurance from their employers. The House’s version could also end up hurting people with pre-existing conditions."
--The US House of Representatives has voted to make cancer patients pay up to $140,000 a year for “insurance”

"Appearing on “Morning Joe” on Friday morning, Representative Steve Scalise of Louisiana didn’t flinch when host Willie Geist asked him a direct question about what would happen if the American Health Care Act—which the House narrowly approved a day earlier—became law.

"So everyone with a pre-existing condition right now who is covered under Obamacare will continue to have coverage?” he asked the congressman, who as House majority whip is the third-ranking Republican in the chamber.

“Absolutely,” Scalise replied.

“Everyone?” Geist pressed him.

“Everyone,” Scalise confirmed.

From off camera, Mika Brzezinski let out a sound that was somewhere between a groan and a gasp."

--From making unrealistic promises to striking back-room deals, the GOP is repeating the same mistakes in trying to repeal Obamacare that they criticized when it was enacted

"Idaho congressman Raul Labrador is facing the ire of his constituents over his vote in favor of the Republican health care bill this week. During a Friday morning town hall, Labrador refuted a voter’s claim that losing health insurance is a death sentence. “That line is so indefensible,” Labrador said. “Nobody dies because they don’t have access to health care.”
Research has consistently linked being uninsured to higher mortality rates. One example in Labrador’s district: 36-year-old Idaho Falls resident Jenny Steinke, who died of an asthma attack in 2015 after being unable to treat her condition due to a lack of insurance."

 "Why would lawmakers back a bill that may strip health care from millions of the poorest citizens (many of whom are Republican voters), could make it cripplingly expensive for tens of millions more, likely leaves them vulnerable in the next election—and that ultimately could be toothless, as fellow Republicans in the Senate are already condemning it?

There is one resounding answer coming from Washington: Because president Donald Trump desperately wanted something he could call a “win.” And while it may not make sense as a political strategy, it does make sense in a context he knows well: reality TV."
--The health care vote shows Trump thinks the US presidency is just another reality television show

"Historically, being a woman in the US has basically been a pre-existing condition, considering 33% of American mothers have had a C-section, 20% of American women will experience sexual assault in their lifetimes, and 25% of women experience violent domestic abuse (compared to one in seven men)."
--Under the new Republican health care bill, being a woman is essentially a pre-existing condition





Wednesday, May 3, 2017

The Necessity of Foreign Keys

My May post @All Analytics.

"Thus, FKs are not just "a good database design habit", but "a user-oriented means" -- as Codd referred to them -- to represent information about relationships between objects of different types in relational databases. The DBMS enforces the matching via a 'referential constraint' on the relations representing the related object types. With relations in normal form and FKs, FOPL and the soundness and simplicity of relational databases are preserved."

Read it all (and please comment there, not here). 



 

Saturday, April 29, 2017

This Week

1. Database Truth of the Week

“… logic—is an analytical theory of the art of reasoning whose goal is to systematize and codify principles of valid reasoning. It has emerged from a study of the use of language in argument and persuasion and it is based on the identification and examination of those parts of language which are essential for these purposes. It is formal in the sense that it lacks reference to meaning. Thereby, it achieves versatility: it may be used to judge the correctness of a chain of reasoning (in particular, a “mathematical proof”) solely on the basis of the form (and not the content) of the sequence of statements, which make up the chain.” --R. R. Stoll

Monday, April 17, 2017

Don't Mix Model with Implementation

Here's what's wrong with last week's database picture, namely:
"When you design your database tables there are some important things to think of:
- Normalize to remove redundant data
- Use the smallest datatype possible
- Create as few indexes as possible, but not too few
- Avoid redundant indexes
- Every table must have clustered index
...
This is important in a normal database but it is even more important in SQL Azure because you have limited space for every database, your connections may be terminated due to heavy use of resources, you pay for what you use and the data that you transfer. You can use the SQL Azure management portal do design your tables or write the T-SQL statement yourself. The syntax to create a table in SQL azure is the same as in SQL server 2005/2008, but not all options are supported in SQL Azure.
...
CREATE TABLE [dbo].[table1]
 ([id] [int] IDENTITY(1,1) NOT NULL,
  [column1] [NVARCHAR](50) NOT NULL,
  [column2] [NVARCHAR](15) NOT NULL,
  [column3] [TINYNT] NULL,
  CONSTRAINT [pk_table1] PRIMARY KEY CLUSTERED ([id] ASC )
   WITH (-- PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF,
         -- FILLFACTOR=80,
         -- ALLOW_ROW_LOCKS = ON,
         -- ALLOW_PAGE_LOCKS = ON,
         -- DATA_COMPRESSION=PAGE)"
--HÃ¥kan Winther, A SQL Azure tip a day – Create a table
Read it all.

Saturday, April 15, 2017

This Week

Database Truth of the Week

"... systems of operations on data are most effective when they are formalisms, in which semantic considerations are unimportant until the formalism is applied to some specific application. In this way, database processing can join the ranks of successful mathematical abstractions. Differential equations, for instance, can be applied to situations ranging from orbit calculations to the quantum mechanics of the atom. The semantics of each application is unique to that application, but the formalism of differential equations is common. The power of the formalism lies in its abstraction from issues of meaning." --H. T. Merrett, Extending the Relational Algebra to Capture Less Meaning

Thursday, April 6, 2017

Understanding Kinds of Keys

My March  Post @All Analytics.

According to search queries hitting @dbdebunk.com, too many data professionals question the mandatory nature of primary keys, ask about changes to them, or prefer surrogate to natural keys. This indicates misunderstanding and misuse of a critical feature that can wreak havoc with inferences made from databases, including analytics. I have explained one function of keys, but there are several types of key that are poorly understood.

Read it all (and please comment there, not here




Saturday, April 1, 2017

"NULL Value" is a Contradiction in Terms

There is nothing wrong with Hugo Kornelis' picture of SQL NULL in NULL: The database's black hole. In fact, I recommend the series of which it is one part. It's the SQL's picture of how to treat missing data that's wrong.
"Let’s first take a look at what NULL is supposed to be. Here is the definition of NULL from the SQL-2003 standard: null value--A special value that is used to indicate the absence of any data value."
While the absence of a value may be represented by some value at the implementation level, I strongly recommend users not think of NULL as any kind of value at the model level. The problems with NULL stem precisely from the fact that it is not a value, but rather a marker for the absence of a value. NULL value is a contradiction in terms that distracts from the problems.

Sunday, March 26, 2017

This Week

1. What's wrong with this picture?

"Things get more complex when NULLable columns are used in expressions and predicates. In a procedural language, this wouldn’t have been a problem--if a procedural program fails to find the information it needs, it enters a conditional branch to handle this situation, as defined by the programmer. In a declarative, set-based language such as SQL, this was not possible. The alternatives were either to have the SQL developer add conditional expressions for each nullable column in a query to handle missing data, or to define a decent default behavior in SQL for missing data so that developers only have to write explicit conditional expressions if they need to override the default behavior." Hugo Kornelis, NULL - The database's black hole

(Nothing wrong with Hugo's picture--in fact, I highly recommend the series of which the source of this quote is one part--only with SQL's picture of relational treatment of missing data).

Sunday, March 19, 2017

New Paper: The Interpretation and Representation of Database Relations

The data management field cannot and will not progress without educated and informed users. Recently I announced UNDERSTANDING THE REAL RDM, a new series of papers that will
  • Offer to the data practitioner an accessible informal preview of David's work.
  • Contrast it with the the current common interpretation that emerged after EFC's passing and to demonstrate the practical implications of the differences.



Saturday, March 11, 2017

What Is a True Relational System (and What It Is Not)

(This is a rewrite of a 12/10/16 post, to bring it in line with McGoveran's interpretation of Codd's RDM.)

Here's what's wrong with last week's picture, namely:
"A quick-and-dirty definition for a relational database might be: a system whose users view data as a collection of tables related to each other through common data values.

The whole basis for the relational model follows this train of thought: data is stored in tables, which are composed of rows and columns. Tables of independent data can be linked, or related, to one another if they each have columns of data that represent the same data value, called keys. This concept is so common as to seem trivial; however, it was not so long ago that achieving and programming a system capable of sustaining the relational model was considered a longshot with limited usefulness.

If a vendor’s database product didn’t meet Codd’s 12 item litmus tests, then it was not a member of the club ... these rules determine whether the database engine itself can be considered truly “relational”. These rules were constructed to support a data model that would ensure the ACID properties of transactions and also eliminate a variety of data manipulation anomalies that frequently occurred on non-relational database platforms (and **still do**)." --Kevin Kline, SQLBlog.com

Thursday, March 2, 2017

The Trouble with Data Warehouse Analytics

My February post @All Analytics

Warehouses are essentially databases biased for some data applications (and against others) and are rooted in poor database foundation knowledge and logical-physical confusion. Even when warehouses consist of relations, warehouse developers often do not understand their precise interpretation. The design is based on various unwarranted or false assumptions about what the data means. The above modeling methods do not allow documenting the transformation itself -- the relational algebra operations that comprise the transformation. But more often than not warehouses do not consist of relations, which are minimally required to be in first normal form (1NF), and are, therefore, not just denormalized, but non-relational. Consequently, all bets are off; sound derivations of correct analytical results are not guaranteed.

Read it all.

Saturday, February 25, 2017

This Week

1. What's wrong with this picture

"A quick-and-dirty definition for a relational database might be: a system whose users view data as a collection of tables related to each other through common data values.The whole basis for the relational model follows this train of thought: data is stored in tables, which are composed of rows and columns. Tables of independent data can be linked, or related, to one another if they each have columns of data that represent the same data value, called keys. This concept is so common as to seem trivial; however, it was not so long ago that achieving and programming a system capable of sustaining the relational model was considered a longshot with limited usefulness." --Kevin Kline, SQLBlog.com

Sunday, February 19, 2017

Simple Domains and Value Atomicity

Revised 2/20/17.

Here's what's wrong with last week's picture, namely:

Q: "I'm currently trying to design a database and I'm not too sure about the best way to approach a dynamically sized array field of one of my objects. My first thought is to use a column in my object to store an array of integers. However the more I read, the more I think this isn't the best option. Concrete example wise, I have a player object that stores 0 to many items, which are represented by an integer. What is the best way to represent this?" 
A: "If a collection of values is atomic, store them together. Meaning, if you always care about the entire group, if you never search for nested values and never sort by nested values, then they should be stored together as a single field value. If not, they should be stored in a separate table, each value bring a row, each assigned the parent ID (foreign key) of a record on the other table that "owns" them as a group. For more info, search on the term "database normalization".

Some databases, support an array as a data type. For example, Postgres allows you to define a column as a one-dimension array, or even a two dimension array. If your database does not support array as a type of column definition, transform you data collection into an XML or JSON support if your database your database supports that type. For example, Postgres has basic support for storing, retrieving, and non-indexed searching of XML using XPath. And Postgres offers excellent industry-leading support for JSON as a data type including indexed support on nested values. Going this XML/JSON route can be an exception to the normalization rules I mentioned above." --StackOverflow.com

Focus on physical implementation ("dynamically sized array field") without well-defined conceptual and logical features it is supposed to represent ("a player object" is hardly enough) and confusion of levels of representation (a real world object does not "store" anything) are always a red flag, an indication of poor grasp of foundation knowledge. So let's introduce some.

Sunday, February 12, 2017

This Week

1. What's wrong with this picture

"If a collection of values is atomic, store them together. Meaning, if you always care about the entire group, if you never search for nested values and never sort by nested values, then they should be stored together as a single field value. If not, they should be stored in a separate table, each value bring a row, each assigned the parent ID (foreign key) of a record on the other table that "owns" them as a group. For more info, search on the term "database normalization".

Some databases, support an array as a data type. For example, Postgres allows you to define a column as a one-dimension array, or even a two dimension array. If your database does not support array as a type of column definition, transform you data collection into an XML or JSON support if your database your database supports that type. For example, Postgres has basic support for storing, retrieving, and non-indexed searching of XML using XPath. And Postgres offers excellent industry-leading support for JSON as a data type including indexed support on nested values. Going this XML/JSON route can be an exception to the normalization rules I mentioned above." --Response to the Quote of the Week listed next, StackOverflow.com


Sunday, February 5, 2017

Meaning Criteria and Entity Supertype-Subtypes

(This is a rewrite of a 2012 post)

Here is what's wrong with last week's picture, namely:

"I have a database for a school ... [with] are numerous tables obviously but consider these:
  • CONTACT - all contacts (students, faculty) has fields such as LAST, FIRST, MI, ADDR, CITY, STATE, ZIP, EMAIL;
  • FACULTY - hire info, login/password for electronic timesheet login, foreign key to CONTACT;
  • STUDENT - medical comments, current grade, foreign key to CONTACT.
Do you think it is a good idea to have a single table hold such info? Or, would you have had the tables FACULTY and STUDENT store LAST, FIRST, ADDR and other fields? At what point do you denormalize for the sake of being more practical?

What would you do when you want to close out one year and start a new year? If you had stand-alone student and faculty tables then you could archive them easily, have a school semester and year attached to them. However, as you go from one year to the next information about a student or faculty may change. Like their address and phone for example. The database model now is not very good because it doesn’t maintain a history. If Student A was in school last year as well but lived somewhere else would you have 2 contact rows? 2 student rows?  Or do you have just one of each and have a change log. Which is best?" --comp.databases.theory

Tuesday, January 31, 2017

Outsmarting the DBMS: Analysts Should Beware

My January post @AllAnalytics:

Last month I alerted you to the failure by data professionals to appreciate the importance, for soundness reasons, of reliance on the DBMS rather than application code for data manipulation and integrity enforcement.
...

Relational design, databases, and DBMSs are the analyst's friends and, given the state of the database field, she/he would be well advised to be as familiar with them as she/he is with analytics and programming, to appreciate  unobvious risks to correctness.

Read it all (and please comment there, not here) 



Sunday, January 29, 2017

This Week

1. What's wrong with this picture
"I have a database for a school ... [with] are numerous tables obviously but consider these:
CONTACT - all contacts (students, faculty) has fields such as LAST, FIRST, MI, ADDR, CITY, STATE, ZIP, EMAIL;
FACULTY - hire info, login/password for electronic timesheet login, foreign key to CONTACT;
STUDENT - medical comments, current grade, foreign key to CONTACT.
Do you think it is a good idea to have a single table hold such info? Or, would you have had the tables FACULTY and STUDENT store LAST, FIRST, ADDR and other fields? At what point do you denormalize for the sake of being more practical?What would you do when you want to close out one year and start a new year? If you had stand-alone student and faculty tables then you could archive them easily, have a school semester and year attached to them. However, as you go from one year to the next information about a student or faculty may change. Like their address and phone for example. The database model now is not very good because it doesn’t maintain a history. If Student A was in school last year as well but lived somewhere else would you have 2 contact rows? 2 student rows?  Or do you have just one of each and have a change log. Which is best?" --comp.databases.theory

Sunday, January 22, 2017

Are You a Thinking Data Professional?

Note: The following was intended as a comment to my post Don't Design Databases without Foundation Knowledge and Conceptual Models  by Todd Everett. He is a reader I deem a "thinking data professional"--always the qualitative rather than quantitative target of my writings and teachings. It merits to be a post in its own right to benefit others.

Monday, January 16, 2017

Don't Design Databases Without Foundation Knowledge and Conceptual Models


"I have two tables, one is product which is a parent table with one primary key and I 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.
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`));

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`));
Please suggest the Pros and Cons of the design, we are following this kind of relationship in my company, as the manager is saying it will give [us flexibility]. I know that if we lose the data from the table, we can't know the relationship between the two tables."--StackExchange.com

Monday, January 9, 2017

This Week

1. What's wrong with this picture
"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.

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`));

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`));
Please suggest the Pros and Cons of the design, we are following this kind of relationship in my company, as the manager is saying it will give us flexible to us. I know that if we lose the data from the table, we can't know the relationship between the two tables."--StackExchange.com

Tuesday, January 3, 2017

Understanding the Relational Data Model: A New Series of Papers

"Nowadays, anyone who wishes to combat lies and ignorance and to write the truth must overcome at least five difficulties. He must have:
  1. The keenness to recognize it, although it is everywhere concealed;
  2. The courage to write the truth when truth is everywhere opposed;
  3. The skill to manipulate it as a weapon;
  4. The judgement to select in whose hands it will be effective, and
  5. The cunning to spread the truth among such persons."
--Berthold Brecht
A rather accurate explanation of why it has been so difficult to dispel the misuse and abuse of the Relational Data Model since inception. To the point that most of its core practical benefits have failed to materialize, with the IT industry regressing all the way back to its pre-relational and even pre-database state:
  • Graph DBMSs;
  • XML;
  • JSON;
  • NoSQL;
  • Application-specific databases and DBMSs;
  • "Unstructured data";
  • No integrity enforcement;
  • A cacophony of imperative programming languages rather than declarative data sublanguages (suffixed with QL, just like old non-relational DBMSs were with /R).