Databases
StackOverflow2013
Postsdbo
POHow to properly query a normalized database
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to properly query a normalized database
    text
    copied!<p>I'm in the process of redesigning a MySQL database that has a table with about 1,500 columns, among other tables. We want to normalize the data in this table by creating a second table that will have a record for each column/row that existed in the initial table. Let's call these tables Master and MasterData. Master will contain the basic information that all records in that table will require. MasterData will contain the values for some additional data pertaining to records in the Master table. So let's say Master will look like this:</p> <pre><code>MasterID Property1 Property2 1 Yes No 2 No Yes 3 Yes Yes 4 No No </code></pre> <p>Let's say MasterData will look like this:</p> <pre><code>MasterID Property Value 1 Property3 Yes 1 Property4 No 3 Property3 No 4 Property7 Yes </code></pre> <p>With me so far? How do I query this data and have only one row returned per matching Master row yet containing all related MasterData information. I've searched and found a couple examples but they take too long to execute on our data. I have created a test MasterData table based on the existing data in our one huge table mentioned earlier. This results in MasterData having about 4.5 million records and the following queries simply take too long to execute and time out. </p> <pre><code>SELECT Property1, Property2, Master.MasterID, GROUP_CONCAT(case when Property = "Property3" then Value end) as Property3, GROUP_CONCAT(case when Property = "Property7" then Value end) as Property7 FROM Master LEFT JOIN MasterData USING (MasterID) GROUP BY MasterID HAVING Property3='Yes' OR Property7='Yes'; </code></pre> <p>or </p> <pre><code>Select * FROM Master AS M, MasterData AS MD1, MasterData AS MD2 WHERE M.MasterID=MD1.MasterID AND MD1.Property='Property3' AND MD1.Value='Yes' AND M.MasterID=MD2.MasterID AND MD2.VAR='Property7' AND MD2.Value='Yes'; </code></pre> <p>Again, our goal is to be able to retrieve all data in MasterData in one row as if it were a column in Master. Is this possible?</p> <p>Any help is much appreciated!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload