July 27th, 2011, 11:36 AM
I've recently taken on a new project and I'm having a little difficulty getting the database set up. The end user needs to be able to create a project based on a type (Ie the user chooses a car project type and fills out the colour, model, year etc).
No problem at all getting that done, but I need administrative users of the site to be able to create new project types and specify the fields for the project.
The only way I can think of doing this is by having a blueprints table which the administrative user can add fields to (using the same example, colour, model, year etc) which would have the field name, type and permissions. This would then be used to create new rows in a fields table when required by the end user.
Main thing I'm worried about is i could end up having a COLLOSAL fields table.
Any advice on this?
July 27th, 2011, 06:08 PM
Your design sounds correct. Also keep in mind that it is a mistake to overly optimize your code too early. Plan for thousands of users now. In the unlikely event that your code grows to need to support millions of users then you can invest the time for a redesign.
You can fit billions of rows in a table. Even if you had 1,000,000 users each with 100 projects each with 10 fields you would still be within the row limit of most database systems. With a modern database engine and file system you can store multiple terabytes of data as well.