help with table/fields structure

[ahabcaptain]

Hi guys, ( i think i posted this in the wrong place first so i'm moving it here.)

 

I'm having a little trouble with getting a dynamic fields concept working for a php/mysql project i'm working on.

 

I made a container structure so when you open up a "container" via php in browser you get all the tables that container owns .. using a simple OwnerID structure a container can own tables and/or other containers. when you open up a container i present the child containers in a sidebar and the tables center view in tabs.. looks very clean and neat.. but i'm having trouble with the tables.. i want the structure to be totally dynamic from the php..

 

i want the tables to have dynamically assignable fields.. so you may create a table called ex "Italian Pasta" or "Vlan Table" and then you have 5-6 types of varchar fields and a few numberic fields etc.. and then you might want to add a field later on or remove a field..

 

the php will need to read out dynamically based on the amount of fields etc.. and how i do that is totally based on how the table structure will look.. and i'm having trouble with the table structure..

 

i want to create new tables on the fly.. so if you want to call the containers "Company:" "Location:" etc.. and then make a vlan database or vrf database for a routing company that'd be easy as pie also..

 

the approaches i have tried;

 

--- multiple tables ---

i made a view that lets the user create a new table phpMyAdmin style with preset field types and simple instructions etc.. and then an edit of each table..

 

this works like a charm, but two reasons this feel wrong;

 

1) you possibly end up with loooooads of tables and i find that messy

2) making a view statement to dynamicallly include unknown fields seems totally impossible.. and that means i'll manually have to populate a search database and i'd like to avoid that.

 

--- single table ---

 

i tried creating one table.. that has

 

ID + RowID + RowName + RowData + OwnerID etc..

 

so effectively building multiple tables ontop of a single table.. but i feel this isn't very scalable since you need multiple consecutive loops/querys to retrieve a single table and it just feels wrong ..

 

but view works like a charm here..

 

.....

 

so i ask.. how can i make dynamically allocateable tables that is scalable and effective and you can still use view for a search table..

 

or am i thinking this wrong somehow? at least it doesn't feel like MySQL or any other database for that matter seems to want to do what i want..

 

hope this was clear enough..

 

[fenway]

Dynamic fields/tables in a bad idea.