0

I have an interesting problem. I have a relational database to which i can use custom scripts to create the tables. It's pseudo SQL and doesn't use standard create syntax. Rather it's fairly limited. What i want to do is store my schema in a MySQL database.

In my custom relational database I have a table called:

Person with fields

  • id as NUMBER not nullable PK
  • name as TEXT (64) characters max
  • year of birth as DATE

So in order to generate the create scripts I thought of using MySQL database to store the schema. For example

I have a MySQL table called custom_table with id and name

e.g. 1, Person would be the first record in it

I have another MySQL table called custom_fields with the following:

  • field_id as int, not null, pk
  • table_name_id, foreign key to custom_table
  • field_name as varchar(255)
  • field_type as varchar(255)
  • is_primary_key as tinyint(1)
  • is_nullable as tinyint(1)

The data set would look like:

field_id table_name_id field_name field_type is_primary_key is_nullable
1 1 id NUMBER 1 0
2 1 name TEXT 0 1
3 1 year DATE 0 1

The part that I am stuck on, is how/where do i store the length of the TEXT field. I have other field types such as decimal which accept additional parameters or default values as well.

I was thinking of maybe have a table called field_date, field_number, field_text which would be related back to the custom_fields table via foreign key relationship but i am unsure how to enforce the fact that each field_id should only exist at most one time in any other table. Any insight would be appreciated or direction to research. My challenge is that I haven't been able to find anything in stack or other sites related to something like this.

  • 1
    Can I take a step back and ask _why_ you want to create a structure like this? What problem are you trying to solve by doing this? – Joe Stefanelli May 28 '21 at 19:45
  • Hey, sure thanks. This software I use has a relational database. I have close to 100+ tables which are a pain to create, update, etc manually from GUI. The software support creating and modifying these tables via javascript (but it's not SQL), rather i pass in the field name, type and other parameters. I want to store my schema somewhere and then dynamically generate these table create scripts. The place my schema is stored needs to be maintainable. My thought was database tables could store this schema. –  May 28 '21 at 19:56
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jun 20 '21 at 00:43

1 Answers1

0

Yes database tables can and do store the schema. It's called the 'catalog'. Every SQL database should have one, and it's maintained by every CREATE TABLE, etc. And you can query it, just like any other table.

If your (rather mysterious) "pseudo SQL" DBMS doesn't do that, get a proper DBMS. Don't try to re-invent the wheel, because trying to maintain a 'shadow' of the actual schema will lead to anomalies.

AntC
  • 2,623
  • 1
  • 13
  • 20