Wt

I need to create a new table in my database

I don't think DBO supports this kind of thing. You'll need to find a new database interaction strategy.

Perhaps you can be persuaded that you don't need a new table? You can certainly restructure the data so that it will fit into an existing table, but that may not be an acceptable option for you. If you provide details, we can try to help you along those lines if you wish.

Hi @lm

Thank you for your response.

Details -

1. The application I am working on aims to serve as a UI for collecting data from user and launching another(black-box) executable with certain values in form of an input file, monitor the progress of launched application(via PID) and then provide the generated results once it finishes.

2. This black-box executable takes as input different geometry files. Data that I am storing in MySQL in form of tables is these geometry structure's attributes(forming column names) and every row corresponds to a specific instance of this geometry structure with certain values to its attributes. For example I have a table called Cuboid model with its different parameters such as dimensions, material its is made of etc.

3. Also this "attribute" information I need to use to display certain UI forms on the application so that users can instantiate an object of this class via UI and the backend table gets populated accordingly.

4. Now I want to enable users to provide any kind of custom geometry object they wish to use. This would involve first creating a MySQL table for that geometry object then a storing data into it via front end.

What possible workarounds do you suggest?

Thanks

This certainly can be done without dynamic tables. One rather mechanical solution: whatever information you would use to create the columns in your tables, make them rows

Consider:

┌───────────────────────────────┐
│ user_polygon                  │
├─────────────┬─────────────────┤
│ id          │ int             │
│ material_id │ int             │
└─────────────┴─────────────────┘
┌───────────────────────────────┐
│ user_polygon_material         │
├───────────────┬───────────────┤
│ id            │ int           │
│ material_name │ varchar       │
│ ...whatever...│               │
└───────────────┴───────────────┘
┌───────────────────────────────┐
│ user_polygon_vertex           │
├─────────────────┬─────────────┤
│ user_polygon_id │ int         │
│ x               │ decimal     │
│ y               │ decimal     │
│ z               │ decimal     │
└─────────────────┴─────────────┘

I suppose this won't be your exact salution, but perhaps you can take it from here? For instance, if you don't know ahead of time how many components will be in a vertex for an object, you can make the field parsable with values like "1.2,1.3,15,12,-2" then parse them in code. Whatever other attributes a polygon has can be held in other tables. Even generic attributes can be held in a generic table:

┌───────────────────────────────┐
│ user_polygon_attribute        │
├─────────────────┬─────────────┤
│ user_polygon_id │ int         │
│ attribute_name  │ varchar     │
│ attribute_value │ varchar     │
└─────────────────┴─────────────┘

(Wow, it was way too fun to make those tables ^_^)

Hello again @lm

Thanks a lot for your response and suggestion.

What I understand you are suggesting is make whatever attribute information I use as my columns as information in the row and then have separate tables for these attributes? and link them via a foreign key say material_id in above example?

The problem I see with this is I do not know preemptive what attributes a custom polygon that user wants will have, so how would I come up with the attribute table in that case.

Another solution that I can think of(or maybe this is somehow what you proposed) is say have a table named - polygon with columns as attribute1, attribute2...etc. Then the rows instead of just haveing values can have parameter_name, value stored in a comma separated way. Later I can access each row from table and populate UI items or vice versa.

Although the first problem I see with this is I have to hard code a maximum upper limit of attributes a polygon can have say till attribute20. And some of these attributes would just be null for those rows whose polygons have lesser attributes.

I am not sure if this is the best design to address my problem. Whats your view?

(I could not figure out[ran out of patience] how to make those fancy tables you just made above !)

:)

a table named - polygon with columns as attribute1, attribute2...etc.

Definitely not. As you say:

I have to hard code a maximum upper limit

which is a deal breaker. That's why I suggested the user_polygon_attribute table above. You can have as many or as few attributes as you like, and they can be in whatever format you like. For instance, say a user creates a polygon with 3 vertices (a triangle): {1,1}, {1,2}, {2,2} that is purple, and an attribute like "location = under the sink". The database might have:

insert into material (id, color) values (1, 'purple');
insert into user_polygon (id, material_id) values (2, 1);
insert into user_polygon_vertex (x, y, polygon_id) values (1, 1, 2);
insert into user_polygon_vertex (x, y, polygon_id) values (1, 2, 2);
insert into user_polygon_vertex (x, y, polygon_id) values (2, 2, 2);
insert into user_polygon_attributes (user_polygon_id, attribute_name, attribute_value) values (2, 'location', 'under the sink');

Hello again

Somehow I am not able to put my head behind this, please bear with me. What I understand is I can have this kind of structure as you already mentioned above -

┌───────────────────────────────┐
│ user_polygon                  │
├─────────────┬─────────────────┤
│ id          │ int             │
│create_date  │ int             │
│name         │ varchar         │
└─────────────┴─────────────────┘
┌───────────────────────────────┐
│ user_polygon_attribute        │
├─────────────────┬─────────────┤
│ user_polygon_id │ int         │
│ attribute_name  │ varchar     │
│ attribute_value │ varchar     │
└─────────────────┴─────────────┘

Now I see that user_polygon_id is the foreign key in second table linking a specific object in table user_polygon to one or more entries in user_polygon_attribute table. But this schema looks somehow incomplete to me. Do I need to have any kind of link back to user_polygon table from user_polygon_attribute table. I see user_polygon_id should be sufficient to do this but I am not able to visualize how this relation would look like in terms of cardinality/degree.

Do I need to have any kind of link back to user_polygon table from user_polygon_attribute table?

As you mentioned:

I see that user_polygon_id is the foreign key in second table linking a specific object in table user_polygon to one or more entries in user_polygon_attribute table.

Nothing else is needed.

I'm not sure what you mean by cardinality or degree. It's a one-to-many relationship. Maybe try sketching out some SQL or an example application to help get your head around the problems?