How to represent a non SQL custom schema structure in a SQL database

Question

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.

Answer 1

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.