Documentation

Table of Contents:


    Setting default table colors on ERDs

    2018-08-23
    Applies to: 7.x (current), Article available also for: 6.x

    When building bigger ERDs you may want to quickly differentiate tables from outside module and database (documentation). You can use colors for this.

    Right now Dataedo does not support this out of the box, but you could set a consistent color scheme using an update statement on the Dataedo repository:

    UPDATE nods
    SET color = CASE 
            WHEN (
                    SELECT count(*)
                    FROM [dbo].[tables_modules] tab_mods
                    WHERE tabs.table_id = tab_mods.table_id
                        AND mods.module_id = tab_mods.module_id
                    ) > 0
                THEN '#487AC6' -- color for current module
            WHEN mods.database_id = tabs.database_id
                THEN '#BACE1F' -- color for other modules
            ELSE '#C62D2D' --color for other DBs
            END
    FROM [dbo].[erd_nodes] nods
    INNER JOIN [dbo].[tables] tabs
        ON nods.table_id = tabs.table_id
    INNER JOIN [dbo].[modules] mods
        ON mods.module_id = nods.module_id
    INNER JOIN [dbo].[databases] docs
        ON mods.database_id = docs.database_id
    -- remove WHERE clause to update the entire repository
    WHERE docs.title = 'Documentation title' 
    -- remove clause below to update entire documentation
        AND mods.title = 'Module name' 
    

    When executed it colors your diagram like this:

    You can change default colors by changing RGB codes in the script.

    Default module colors

    There's also more advanced technique - you could set a specific color for each of your modules using custom fields introduced in version 6.0.

    You can add a new custom field named 'ERD node color', set it applies for modules only:

    and provide a color code (RGB hexadecimal format: #FFFFFF ) for each module:

    Then you need to find out the actual table column in which the custom field value is stored using:

    SELECT [field_name] FROM [dbo].[custom_fields]
       WHERE title = 'ERD color'
    

    For me, the result was field1. Using it you can just update nodes with colors as defined in modules:

    UPDATE nods
    --change field1 to the result of previous select
    SET color = left(mods.field1, 7)
    FROM [dbo].[erd_nodes] nods
    INNER JOIN [dbo].[tables] tabs
        ON nods.table_id = tabs.table_id
    INNER JOIN (
        SELECT table_id
            , min(module_id) module_id
        FROM [dbo].[tables_modules]
        GROUP BY table_id
        ) tab_mods
        ON tabs.table_id = tab_mods.table_id
    INNER JOIN [dbo].[modules] mods
        ON mods.module_id = tab_mods.module_id
    

    Do note that if table is in more than one module, color of the older module will be chosen.

    Found issue with this article? Comment below
    Comments (0)