Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <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> </blockquote> <p>Without completely understanding your goal, I'm going to go out on a limb and say it's probably <em>possible</em>, strictly speaking. But it's not likely to be possible in any practical sense. Performance will probably be abysmal in even the best case (just one or two properties); in the likely case (what, between 30 and 500 properties) you might take down the server altogether.</p> <p><em>Normalized</em> doesn't mean "creating a second table that will have a record for each column/row that existed in the initial table". It doesn't mean anything even remotely <em>like</em> that. But it's <em>possible</em> that normalization will actually solve your problem. (In my experience, most database problems are structural.)</p> <p>What you have proposed here is a solution that isn't working well to a problem you haven't stated. To get the most out of the expertise on StackOverflow, state the problem you're trying to solve as well as the solutions you've tried.</p> <p><a href="http://en.wikipedia.org/wiki/Database_normalization" rel="nofollow">Wikipedia article about database normalization</a></p> <hr> <p>If you start with a table like this . . .</p> <pre><code>create table master_data ( master_id integer not null, property_name varchar(30) not null, property_value boolean not null default true, primary key (master_id, property_name) ); insert into master_data values (1, 'Property3', true), (1, 'Property4', false), (3, 'Property3', false), (4, 'Property7', true); </code></pre> <p>. . . then you can get all the properties for all the things with a simple query. (Assumes all your properties are Boolean.)</p> <pre><code>select * from master_data order by master_id, property_name -- 1 Property3 t 1 Property4 f 3 Property3 f 4 Property7 t </code></pre> <p>Application code can loop over that pretty simply. And you might be able to delete all the rows where property_value is false. </p> <p>This structure allows an unlimited number of properties for each thing. But your requirements to a) return an arbitrary number of properties in a single row, and b) make minimal changes to application code have to change. There's no way around that.</p> <hr> <p>If your table contains these rows . . .</p> <pre><code>insert into master_data values (1, 'Property3', true), (1, 'Property4', false), (3, 'Property3', false), (4, 'Property7', true), (1, 'Property7', true); </code></pre> <p>here's one way to get a set of "things" that qualify, and join that set to the table of master data. </p> <pre><code>select md.* from master_data md inner join (select master_id from master_data where ( (property_name = 'Property3' and property_value = true) or (property_name = 'Property7' and property_value = true) ) group by master_id having count(*) = 2 ) cd on (md.master_id = cd.master_id) </code></pre> <p>For what it's worth, normalization is still <em>probably</em> your best bet for long term maintenance and performance. This kind of structure (above) is not normalized; performance is generally poor with large sets of data. (PostgreSQL with the optional hstore module might be better than MySQL at this.)</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to properly query a normalized database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike Sherrill 'Cat Recall'
    1. USMike Sherrill 'Cat Recall'
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis is my first post on Stackoverflow and I'm not quite sure how the comments work but I'll repeat what I just wrote above. Our problem is that we're running out of columns (properties) in the Master table and need a way to continue to add 'properties'. What is the best way to do this? I understand this isn't the definition of 'normalized' but I've seen this technique used in ecommerce web apps before so we thought we'd try it here. I'm definitely open to any other suggestions you may have or links you could point me to.
      singulars
      1. PO
      1. USmrceolla
    2. CO[MySQL has column and row size limits.](http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html) AFAIK, no SQL dbms supports continuously adding columns forever. You *might* be able to add another table with basically the same structure and exactly the same primary key data type (not autonumber, though) and link them with a foreign key constraint (with INNODB, not with MyISAM). That would give you a bunch of columns you could use in the second table, but I'd consider this a Band-Aid, not a solution. (JOIN the two tables at run time; don't exceed column or row size limits, though.)
      singulars
      1. PO
      1. USMike Sherrill 'Cat Recall'
    3. COAgreed. I think a 2nd table of similar structure would be a band-aid. We could hit a column limit on the second table and this problem would arise once again and we would then have to go into the code and adjust all of the queries...again. Can you think of a 'solution' to this problem? Is there anything I could further explain?
      singulars
      1. PO
      1. USmrceolla
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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