MySQL database with user created tables with custom column numbers

Question

I have a person table and I want users to be able to create custom many to many relations of information with them. Educations, residences, employments, languages, and so on. These might require different number of columns. E.g.

Person_languages(person_fk,language_fk)
Person_Educations(person,institution,degree,field,start,end)

I thought of something like this. (Not correct sql)

create Tables(
             table_id PRIMARY_KEY,
             table_name_fk FOREIGN_KEY(Table_name),
             person_fk FOREIGN_KEY(Person),
             table_description TEXT
            )

Table holding all custom table name and descriptions

create Table_columns(
                     column_id PRIMARY_KEY,
                     table_fk FOREIGN_KEY(Tables),
                     column_name_fk FOREIGN_KEY(Columns),
                     rank_column INT,
                    )

Table holding the columns in each custom table and the order they are to be displayed in.

create Table_rows(
                  row_id PRIMARY_KEY,
                  table_fk FOREIGN_KEY(Tables),
                  row_nr INT,
                 )

Table holding the rows of each custom table.

create Table_cells(
                   cell_id PRIMARY_KEY,
                   table_fk FOREIGN_KEY(Tables),
                   row_fk FOREIGN_KEY(Table_rows),
                   column_fk FOREIGN_KEY(Table_columns),
                   cell_content_type_fk FOREIGN_KEY(Content_types),
                   cell_object_id INT,
                  )

Table holding cell info.

If any custom table starts to be used with most persons and becomes large, the idea was to maybe then extract it into a separate hard-coded many-to-many table just for that table.

Is this a stupid idea? Is there a better way to do this?

Eugen Rieck · Accepted Answer · 2012-12-20 11:10:12Z

2

I strongly advise against such a design - you are on the road to an extremely fragmented and hard to read design.

IIUC your base problem is, that you have a common set of (universal) properties for a person, that may be extended by other (non-universal) properties.

I'd tackle this by having the universal properties in the person table and create two more tables: property_types, which translates a property name into an INT primary key and person_properties which combines person PK, propety PK and value.

If you set the PK of this table to be (person,property) you get the best possible index locality for the person, which makes requesting all properties for a person a very fast query.

edited Dec 20, 2012 at 11:10

answered Dec 20, 2012 at 10:05

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Aldridge Over a year ago

+1 And here's an instructional tale: simple-talk.com/opinion/opinion-pieces/bad-carma

Collectives™ on Stack Overflow

MySQL database with user created tables with custom column numbers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related