Designing ERDs within DDL (Create Table Statement)

[WWGD]

Hi again,
just curious as to whether it is possible to use DDL (Data definition, i.e., non-GUI ); more specifically to add something to the usual create table statements to express the relation between tables, i.e., these tables being either in 1:1, M:1 or M:N relation (so that a new, bridge table with default primary keys would be created )?

We seem to already have gone a good part along the way, having info on FKeys and what fields
they reference in other tables. Is there additional code to allow for this, i.e., to allow for the description of these relations and to ultimately have as an output the ERD of the relation?

Thanks.

 

[Borg]

Auto-increment?

 

[jedishrfu]

You can specify that keys are unique or not in the table and you can specify whether fields can be null or not.

Are you designing a database schema or trying to understand database programming?

Have you looked at star schema design?

 

[WWGD]

You can specify that keys are unique or not in the table and you can specify whether fields can be null or not.

Are you designing a database schema or trying to understand database programming?

Have you looked at star schema design?

Hi, both actually, I am trying to understand in general and use for designing schemas. It would be great to hve a full ERD as output without the need to use GUIs.

 

[jim mcnamara]

You can use metatdata tables (in Oracle example: all_tab_columns, all_tables, etc.) to use the 'start with connect by' tree creation SQL statement to create an ERD.

You've essentially asked the same question multiple times. Question== "I want to automagically create an ERD".

Answer:
I do not know mysql (what you seem to use) from beans. But, I do know there are free tools to analyze table metadata out there. Start googling, some terms:
database, metadata, analysis, entity relationships.

Personally, I find most ERD's for commerically produced database application systems to be a digression. As well as requiring a huge wall to display the darned thing on paper. Whenever something trancends normal human perception limits, its usefulness is quite limited.

There is a reason for metadata tables other than to keep the db engine amused: ERD information is one select statement. How do you suppose, for an existing application, someone created an ERD to start with?

 

[WWGD]

You can use metatdata tables (in Oracle example: all_tab_columns, all_tables, etc.) to use the 'start with connect by' tree creation SQL statement to create an ERD.

You've essentially asked the same question multiple times. Question== "I want to automagically create an ERD".

Answer:
I do not know mysql (what you seem to use) from beans. But, I do know there are free tools to analyze table metadata out there. Start googling, some terms:
database, metadata, analysis, entity relationships.

Personally, I find most ERD's for commerically produced database application systems to be a digression. As well as requiring a huge wall to display the darned thing on paper. Whenever something trancends normal human perception limits, its usefulness is quite limited.

There is a reason for metadata tables other than to keep the db engine amused: ERD information is one select statement. How do you suppose, for an existing application, someone created an ERD to start with?

Sorry, I was thinking of MSSQL2014 . I just thought that since DDL statements go so far towards laying out the needed specs for an ERD, that it may be possible to go all the way and implement a design. But, yes, for reasonable ERDs with, say, less than 10 tables.

 

[jim mcnamara]

Thanks to PAllen - also see this for the same CTE query with other engines

https://msdn.microsoft.com/en-us/library/ms186243.aspx

 

[pbuk]

Not quite sure what you are getting at: are you familiar with the concept of a CONSTRAINT? You can use a CONSTRAINT to link the foreign key column in the 'many' side of the relationship to the key column on the 'one' side. You can then use a tool to create an ERD from the schema (e.g. SqlServer's Diagrammer).