Say I have a database that will have a changing number of tables each with different columns. What would be the best way to structure this?

I'll give an example to explain what I mean. Right now, say I have 2 tables. I'll just name the columns based on their data type to make things easier.

table_1 (id1_int, varchar, longtext)

table_2 (id2_int, date, varchar)

One way I thought of would be to have a table that holds information on the structure and then create each table as needed using PHP. The types could be moved to their own lookup table, but I'll exclude that for simplicity. This idea is better for performance, but I think it could get annoying if the number of tables grows too large.

The second idea I had would be to have separate tables for each data type, and then just rebuild the table. I know that there's redundant information with regards to (table_id, column_id) but again, I'll ignore it for now. This idea has the advantage in simplicity, but I would imagine that the performance would be awful.

A third idea, which I want to avoid at all costs, would be to make everything a string and then use PHP to re-convert the data when reading from the database.

Not all of the data types I will be using will be the standard SQL ones. I may for example, want a list type that can hold a variable number of elements, in which case I'd probably need a separate list_element table and then just have the tables above point to the list_id in that table.

Thanks in advance for any help anyone can give me.

it would help to know what type of data is going into the tables.

In my experience, structures with variable numbers of tables usually end up pretty complicated. Maybe there's an easier way to model the information

Looking at the way you've presented the problem, I also think you need to rethink your model.

Come up with a static schema, and have your rows increasing in each of those tables, as opposed to your number of tables increasing over time.

If table 1 is ever going to have the same schema as table N further down the track but with a different table name, it feels like something has gone wrong. If you already know the datatype of table N (as you suggested putting the datatypes into a table to dynamically create them later on) then it seems you know the structure and can create it from the start.

There already IS a table which stores the column names and datatypes for tables, in the system tables for whatever DB you are using.

Perhaps I don't really understand the problem, but I'm imagining you doing something like:

and then:

... which is very wrong.

Don't be afraid to have purely relational tables. Good luck!

Basically, I want the "tables" to each hold a general category of objects, with the columns being different characteristics of the objects. I use quotes because I'm starting to think it might be better to not actually use a table for each category, but instead simulate having a table, like in example #2.

I can't. That's the problem. Here's an example to show why I'm having issues.

Let's say I want to store information on animals, plants, and vehicles, so I make the following tables:

animals:
- name (text), number_legs (int), weight (float), color (text)
plants:
- name: (text), color (text), edible (bool)
vehicles:
- name (text), number_wheels (int), weight (float), length (int), purpose (text)

But later down the line, I want to add a table for computers with (cpu (float), ram (int), os (text), price (float)) as the columns. Obviously it's not going to fit under any of the other categories, so I'd have to create a new table. I agree that growing the number of tables over time is not the best, but the same goes for splitting the data up among different tables and having to recreate it each time I want to retrieve data.

At the beginning, there's no way to know all of the different objects that people will want to classify using the database, so I cannot have a set schema. What I'm trying to do is find a good structure that I can use so that any object-characteristic set can be listed. I hope this clarifies things.

I have written applications like this.

you create "definitions" tables - a header and a detail

DefHeader
HdrID (int), Name (char)
stores "Animals", "Plants", etc.

DefAttribute
DefID (int), HdrID (int), Name (char) DataType (int)
Stores "Number_Legs" or "Color".

The datatype tells you what types of values can go into this column. It might be a string, a number, a date, or a value from a list.

This defines the types of objects. When a user wants to create an animal type named "Cat", you store the attribute values into other tables that relate to the two above.

DefHdrInstance
DefAttrInstance


I will warn you that while you can get a system like this up and running, it becomes a major pain in the butt to implement things like:

1. business logic: when properties depend on each other, like if the value of RAM can't be more than "4" because the os value is "windows xp". Where do you put this type of logic, when the data models themselves are all data-driven? What I did was end up adding a bunch of code in my user interface that was like "if the user just changed a property named "ram", then see if there's a property named "os", and, if so, then clear the value if it's currently equal to "windows xp". This gets messy in a big hurry.

2. nice reports - using a traditional reporting tool like Crystal is impossible when your data is structured this way, because your data is arranged "down" in the database (in rows) instead of "across" in columns.

Oh ok, that is similar to my example 2. I realized that relational databases are pretty poor for this, but since this is going to be for a website, I don't think I have that much choice. I just wanted to ask on the forums first in case there was some method that I was completely missing. Fortunately, I don't think I'll need to input any business logic (crosses fingers). It should just be straight up storing data for my purposes. I really appreciate the input.

Another question. Given that each "row" is now split among different tables, my queries are no longer atomic. Is there a way to make them atomic using the MySQL MyISAM engine, or are my options using the InnoDB engine vs PostgreSQL? Sorry for all the weird questions. I'm just trying to make sure I have everything down before I start on this project. The last thing I want to do is code half of it and then realize that I need to restructure everything, or switch my DBMS.