PHP Dynamic 'Log Books'

[Asheeown]

I'm making a log book system and I'm in need of a good dynamic fields idea.

 

In this system a user can create a new log book.  In that creation I want him/her to be able to specify custom fields for the logs in that book.  So for instance if I was making a log book on when I took my dog for a walk, I might want the following fields:

 

Location - text

BathroomStops - int

FirehydrantsSeen - int

GoodExercise - bool

* Obviously joking in most of these

 

My first choice to myself was new MySQL tables for each book.  Just take the fields they entered with a data type and create a new table for that specific log book.

 

My second choice was to create tables that the data would be stored in with an abundant amount of columns.  For example, 20 or 30 columns, so when the user entered the fields they wanted I could have a definitions table telling me which column did what.

 

The more I go on about this is, it seems like a MySQL question, but in reality PHP will be determining what fields to show on the screen and so on.  So really I'm looking for a simple efficient solution, no code, just the idea.

 

[ignace]

fields (field_id, field_log_id, field_name, field_type)

logs (log_id, log_name)

fields_values (value_field_id, value_value)

 

insert into logs  (1, 'Walk The Dog');

insert into fields (1, 1, 'Location', 'String[200]'),

                  (2, 1, 'BathroomStops', 'Integer'),

                  (3, 1, 'FirehydrantsSeen', 'Integer'),

                  (4, 1, 'GoodExercise', 'Boolean');

insert into fields_values (1, 'Pittsburgh'), #Location

                          (2, '3'), #BathroomStops

                          (3, '15'), #FirehydrantsSeen

                          (4, 'False'); #GoodExercise

 

Your application would handle String[200], Integer, and Boolean.

[Asheeown]

fields (field_id, field_log_id, field_name, field_type)
logs (log_id, log_name)
fields_values (log_id, value_field_id, value_value)

insert into logs   (1, 'Walk The Dog');
insert into fields (1, 1, 'Location', 'String[200]'),
                   (2, 1, 'BathroomStops', 'Integer'),
                   (3, 1, 'FirehydrantsSeen', 'Integer'),
                   (4, 1, 'GoodExercise', 'Boolean');
insert into fields_values (1, 1, 'Pittsburgh'), #Location
                          (1, 2, '3'), #BathroomStops
                          (1, 3, '15'), #FirehydrantsSeen
                          (1, 4, 'False'); #GoodExercise

 

Just left out the log ID in the field values table.

 

So you think this would be an efficient method of doing this even with say 200 log books and around 20-50 entries a day on each?

[Asheeown]

Bump

[ignace]

Just left out the log ID in the field values table.

 

I left it out because it doesn't needed it. value_field_id links to field_id.

 

So you think this would be an efficient method of doing this even with say 200 log books and around 20-50 entries a day on each?

 

Try it!

[Asheeown]

Try it!

 

When I first read that I was like "crap, wish I knew if it was a sure more efficient way to do this."  But this is actually a good opportunity.  I'm going to create a structure for two methods, yours and creating a table for each log book.  Then I'm going to make a script to create a few hundred log books in each and start populating them with thousands of rows.  Then I'll run a stress test script against both my webservers and see each reacts to the processing loads.

 

I think querying one table for all log entries might start to bog the query result speeds but I also think too many tables will bog MySQL in general so we'll see how it comes out.

 

Thanks for the suggestion.  I'll mark this as solved and most likely post my results when I'm done for future reference.

[ignace]

I can already tell you your solution is going to be faster but a hell to maintain if they push through changes. Find a balance that works between a normalized and de-normalized structure

[Asheeown]

I can already tell you your solution is going to be faster but a hell to maintain if they push through changes. Find a balance that works between a normalized and de-normalized structure

 

Absolutely, and I figure designing and testing the structure of each will give me a nice final solution in the end.  Maybe even a new combination of sorts.  Well, I'll just have to find out.