View Full Version : Storing data of varying type.

03-24-2009, 03:29 PM
I am creating a flexible web application in PHP and am a little confused as to how to structure my database. I am trying to store what I am calling "data models" (since I don't know the correct technical term!) in a database which will describe what something is made up of, as well as the data for that object.

For example:

Apple is made up of:
- One Textfield
- One Image
- Two Booleans

So in my database so far, I have two tables:

Data Models
- id
- name

Data Model Fields
- id
- model_id (relation to the data models table above)
- field_name
- type (which I'd enter something like text field / image / boolean)

These two tables map out my data model nicely, but now I need to store the actual physical data. So lets say I create a new Apple with the following data:

- Test
- image1.jpg
- true
- true

I need to store that data in my database somehow. Obviously the immediate solution is to create a new table which relates to the fields table and store all the data in a column typed as text... but I am worried of the performance hit my application will take because of this. For example, storing a 0 or a boolean in a column typed text is way overkill!

Can anyone help suggest a way in which I can structure my database to allow for a similar model based work flow?

Thanks much!

Old Pedant
03-24-2009, 10:11 PM
So how many different data models do you expect to have??

The obvious answer, if you say "not too many", is to simply use CREATE TABLE to create a table exactly designed to hold an "Apple" object.

If you say "thousands" then the problem is thornier.