Level 80
@ViktorAlm I’m struggling to understand the use case for your application, but my gut feeling is that users should not be creating tables in your database.
Apr 16, 2015
3
Level 3
User-created tables, database design
Hey!
I made a php application a while back to create, update and search data in user-defined tables. The different columns can be of various types like multiple select, textarea, text and so on.
We've been using it for member lists with volunteers for my university's different societies and for some archives inventory, but my goal is to build a general purpose database storage tool for collaboration.
The way the existing application works is that when a user creates a new table the application creates a new table in MySQL and saves the data about the different column types and other things required to display the table in another table.
Right now it looks something like this
ex. Volunteers registration
Code created table: 1
| Id | 1 | 2 | 3
|----- | -------- | ----------- | -------------
| 0 | XX XX | XX@XX.com | Cooking, Recruiting, IT
| 1 | XY XY | XY@XX.com | Athletics, Bartender
TableOptions
| Id | TableId | ColumnId | Name | Type | Options | Required
| -- |------- | ------ | ---- | -------------- | -------- |-------
| 0 | 1 | 1 | Name | Text | null | 1
| 1 | 1 | 2 | Email | Text | null | 1
| 2 | 1 | 3 | Volunteer | Multiple select | Cooking, Recruiting, IT, Athletics, Bartender | 1
With these tables a form for adding data and a jQuery Datatable for displaying data is created with my not-so-great php code. This has been bugging me for the last few years and I want to build a new version with Laravel. What the user sees in a jQuery dataTable
| Name | Email | Volunteer
| -------- | -------------- | -------------------------------------
| XX XX | XX@XX.com | Cooking, Recruiting, IT
| XY XY | XY@XX.com | Athletics, Bartender
What is the best solution for an application like this where users can create and update tables in Laravel?
I've asked my SQL teacher and he couldn't give a definitive answer. His solution was to have one table with columns a-z and use another table like my TableOptions to build the different tables. I dont really like any of the solutions and there must be a better way of doing this? and how would I go about doing it in Eloquent? Is it possible? Do I have to go NoSQL?
It would be great if someone more experienced could point me in the right direction with an example or a book to read or a phrase to google. Anything is appreciated.
Level 3
Hey thanks for your reply, think something like https://www.knackhq.com/ but simpler.
My users need to create tables where others users can search and edit the data in the tables. The users need to be able to add and remove columns themselves and specify which type of input the different columns should have.
I'm wondering how I should design the database and build my models with eloquent and what the cleanest solution is.
Level 2
@ViktorAlm I read your problem and did a little study about it, test knackhq also. I didn't work with this types project's before. Eloquent works great with developer defined database design, in eloquent you can simply define your relationship, access it object oriented style. In your application you need a dynamic database design system tool/package/API not only eloquent, i don't know about is types of package/tool already exists yet, or you can develop it first. when user will create a database, then this tool automatically defines the eloquent model, and define also user created database relationship, and provide a restful API for accessing data easily to users. Everything will happen behind the scene. I need a good research about it, if get any new idea, resource or tool, i will contact with you.
Please or to participate in this conversation.