data formatting woes

digiboi

Hi all,

CONTEXT: I'm building a PHP-MySQL system in which I need to associate certain tables within a single database with user-defined "projects". The data concerning the projects is in a "proj_master" table.

QUERY1: Can anyone think of a way to easily associate a table with a "project". Methods I have considered but with which I'm not satisfied are:
1. putting a field in each table that identifies the project with which it is associated --reason for dissatisfaction: causes lots of redundant data because project name will be in every row
2. mandating users must put the nae of the project in the table name when they create the tables--reason for dissatisfaction: I have no idea how to perform a query like: SELECT all tables WHERE beginning of table name = project name

QUERY2: If in my "proj_master" table I have only one field per project (per row) and in that field is a comma-delimited string of the tables, I can explode the string into an array. But how can I make sure that when a non-technical user enters a new table to be associated with the project, a comma gets inserted between the last old table and the new one?

Thanks so much for any and all aid! 🙂

digiboi

Someone asked for further clarification...

I'm trying to develop a system in which I can set up a single database for a client and they can set up multiple projects within their database, say a website, an intranet, and something else. I want to do this because most webhosts accross which I've come charge additional fees per database (but not per table!).

Example Tables:

table1
id | field1 | field2 | field 3

table2
id | field1 | field2 | field 3

proj_master
proj_id | proj_name | proj_description | tables in this project?

My goal is to associate table1 with project 2 and table 2 with project 9, etc., so that the admin can log in and get a "system overview" with things like system status and tables and files organized by project (this last part is that to which my question relates).

Thanks!

digiboi

Anon

Does it have to be a single database? Might be easier to let each project be a separate database. I doubt there is much extra overhead.

You can store the database names in your project master table, but I would stay away from the comma delimited approach. Store one value per row, use as many rows as you need.