Data Model Feature/Flaw

[dbo]

This is sort of an application/datamodel/mysql type of question I guess. Without going into a lot of immense detail I've designed a pretty slick datamodel that lets me define any object type on the fly, and then create CRUD forms accordingly, all within a 12 table structure.

 

The way this flexibility is created is by storing each field as it's own record, so for example:

 

A typical news record might look like this:

 

1, This is a title, This is a story, This is an author.

 

In my model it would basically return like this:

 

1, 1, This is a title

2, 1, This is a story

3, 1, This is an author.

 

I've got a "Record ID" associated with each of these (the 1), so I'm able to aggregate the individual fields into a single record, but I have to do this via PHP code instead of on the database. For moderate sized data this isn't a problem, but as it grows it very well could be... and I lose some flexibility.

 

My question is, do any of you guys know of a way to take a query that comes back as is illustrated in my example, and turn that into a single record (first example). Even if it's an expensive subquery of sorts, it has to be more efficient than doing it in code.

 

Any thoughts, approaches, suggestions on potential solutions, or things I should look out for?

[trq]

I'm sure your question might get a better response in the mysql board, but as it is a design question I'm happy to leave it here.

 

Without being able to offer up a definate solution, and not really being any sql expert. I would suggest a UNION may be what your after. Given a table structure such as.

 

CREATE TABLE foo (
  id INT AUTO_INREMENT,
  record_id INT,
  k VARCHAR(80),
  v TEXT
);

 

With the following records.

 

INSERT INTO foo (record_id,k,v) VALUES (1,'title','a new article');
INSERT INTO foo (record_id,k,v) VALUES (1,'data','here is our nice new article');
INSERT INTO foo (record_id,k,v) VALUES (1,'author','thorpe');

 

Our UNION might look like....

 

(SELECT v FROM foo AS k WHERE k = 'title' && record_id = 1)
UNION
(SELECT v FROM foo AS k WHERE k = 'article' && record_id = 1)
UNION
(SELECT v FROM foo AS k WHERE k = 'author' && record_id = 1)

 

As I said, I'm no sql expert, this is untested and may be far from working. Just an idea though.

[dbo]

Hrmm, that could get ugly quick, but might be just what I'm looking for. I probably won't get to experiment much until tonight, but it will give me something to think about. If I'm able to get the records correctly "rolled up" into a single result set, much of the other features will be a lot easier to maintain (ie. paging/sorting/general reporting, etc)

 

Thanks for the insight.

[dbo]

Bummer, your example seems to still store the data in individual rows instead of a single record.

[dbo]

What I really need to do is run 3 separate queries and be able to join them, rather than union them. Then I could query each "field" record and it's id, and then join the result sets on the record id. Unfortunately, I'm not sure that you can join result sets, only tables.

[trq]

Yeah, I tested my code this morning, no go. You might get a better responsed in here though. Sorry.

[dbo]

SELECT f1.v As `Title`, f2.v As `Story`, f3.v As `Author`
FROM foo f1, foo f2, foo f3 

WHERE f1.k = 'title' AND
      f2.k = 'data' AND
      f3.k = 'author'

 

This is pretty clunky, but a similar approach seems as though it may have some promise. This actually works as expected, but the where clause will probably have to be extended when the table has more results.

[dbo]

Unfortunately for me, in my real scenario the initial data is actually coming from a couple of tables and as far as I know you can't alias a result set, just a table/field name. So this doesn't work:

 

SELECT f1.v As `Title`, f2.v As `Story`, f3.v As `Author`
FROM (SELECT * FROM foo) f1, (SELECT * FROM foo) f2, (SELECT * FROM foo) f3

WHERE f1.k = 'title' AND
      f2.k = 'data' AND
      f3.k = 'author'

[dbo]

Crisis averted. Adding 'As' seemed to fix life.

 

SELECT f1.v As `Title`, f2.v As `Story`, f3.v As `Author`
FROM (SELECT * FROM foo) As f1, (SELECT * FROM foo) As f2, (SELECT * FROM foo) As f3

WHERE f1.k = 'title' AND
      f2.k = 'data' AND
      f3.k = 'author'

 

 

[aschk]

Ah hah, i see what you're after now. Your intial post confused me because I was unsure whether your model was your database or your record was your database. However now clarity has kicked in I can see the "record" is what you want out, and the model is the data that is currently stored inside.