Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Designing ERDs within DDL (Create Table Statement)

  1. Dec 21, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. Dec 21, 2015 #2

    Borg

    User Avatar
    Gold Member

  4. Dec 21, 2015 #3

    jedishrfu

    Staff: Mentor

    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?
     
  5. Dec 21, 2015 #4

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  6. Dec 21, 2015 #5

    jim mcnamara

    User Avatar

    Staff: Mentor

    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?
     
  7. Dec 21, 2015 #6

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  8. Dec 21, 2015 #7

    jim mcnamara

    User Avatar

    Staff: Mentor

  9. Dec 23, 2015 #8
    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).
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Designing ERDs within DDL (Create Table Statement)
  1. Switch Statements (Replies: 1)

  2. Goto statements (Replies: 25)

Loading...