Common schema in different Databases

  • lfernigrini

    Valued Member

    Points: 65

    Hi, I need to include a very simple schema in all of my database projects. It will have 3-4 tables to log some special events. I want to include it on my current projects, but keep a single definition.

    Is there a simple way to obtain that? I know I can reference a datpac or another project , but I do not want the schema to be considered "outside" each of the databases, I want it to be part of them.

     

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    Can you not just include a script to create the tables?  Alternatively, you could put the tables in the model database, so long as you don't create any other databases on the instance.

    John

  • Phil Parkin

    SSC Guru

    Points: 244669

    I can't think of a way of doing this which allows the schema objects to be both part of every database project and maintainable from a single place.

    I don't think that John's suggested ideas work very well, from a source-control point of view.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • lfernigrini

    Valued Member

    Points: 65

    We will have several database on the same instance, all of them should have the same set of tables, and there is code that references them (triggers on different schemas that insert into those "common" tables).

    I can add them with a shared post deployment script, but the triggers in the project would fail since they won't be able to "see" the tables...

    I have though of storing the tables in a separate database, common to all databases on the instance (and referenced on each database project), but the problem is that if any database needs to be moved to another instance/server then the audit data it generated will remain on the old common database  that is the reason i want to have those tables as a schema on each database and not a shared database.

    They are pretty simple objects, 3-4 tables with a PK, no FK, and a couple of indexes. but I have 20+ database where I need to add them, and I do not want to repeat the definition on the 20 of them.

     

     

     

  • Phil Parkin

    SSC Guru

    Points: 244669

    I can add them with a shared post deployment script, but the triggers in the project would fail since they won't be able to "see" the tables...

    Not ideal, but could you also put the triggers in the PD script?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • lfernigrini

    Valued Member

    Points: 65

    No, they are more than 2000 triggers (1000+ tables, triggers for insert and delete that record some specific details when some specific conditions are met...)

  • frederico_fonseca

    SSChampion

    Points: 14710

    you can try the following.

    create a folder where you put the create scripts for those tables

    on the projects where you need those tables ensure you have the folders setup correctly for the schema where the tables will reside

    then on VS go to that folder within each project, right click->Add Existing Item -> select on the bottom right dropdown - Add As Link

     

    that should do it

  • Phil Parkin

    SSC Guru

    Points: 244669

    frederico_fonseca wrote:

    you can try the following.

    create a folder where you put the create scripts for those tables

    on the projects where you need those tables ensure you have the folders setup correctly for the schema where the tables will reside

    then on VS go to that folder within each project, right click->Add Existing Item -> select on the bottom right dropdown - Add As Link

    that should do it

    If this works, it's almost perfect for your scenario; awesome! Never knew that was a possibility, and I've been using SSDT database projects for 10 years.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • frederico_fonseca

    SSChampion

    Points: 14710

    Phil Parkin wrote:

    frederico_fonseca wrote:

    you can try the following.

    create a folder where you put the create scripts for those tables

    on the projects where you need those tables ensure you have the folders setup correctly for the schema where the tables will reside

    then on VS go to that folder within each project, right click->Add Existing Item -> select on the bottom right dropdown - Add As Link

    that should do it

    If this works, it's almost perfect for your scenario; awesome! Never knew that was a possibility, and I've been using SSDT database projects for 10 years.

    it works great - schema compare with real db thinks the objects are there, code build includes them on scripts as if the file was on same folder as the others.

    but it not a well know functionality.

    Now if MS could fix bloody circular project references it would be great.

  • lfernigrini

    Valued Member

    Points: 65

    Seems a good idea. I did not knew about this, but seems to be the behavior I need.

    The only problem I see is that the stored path must be relative since this can be deloyed from different PCs, and will be stored on GIT. I will do some testing and let you all know!

  • Phil Parkin

    SSC Guru

    Points: 244669

    Now if MS could fix bloody circular project references it would be great.

    Can you provide a practical example where circular (as defined by MS in this case) references are an example of good database design?

    I've certainly had a good deal of trouble getting legacy DBs imported into DB projects, as a result of circular references, but in these cases the best solution was to re-architect the DBs because those references were bad design.

    Apologies: I don't want to hijack the thread, but this is an interesting topic.

    • This reply was modified 4 months, 1 week ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • frederico_fonseca

    SSChampion

    Points: 14710

    Phil - it was combination of 3 db's for a migration project (insurance company).

    • transientdb - source data
    • persistentdb - etl db - had synonyms to both transient and product - main ETL process happened here
    • productdb - final database - as part of the final process (COBOL .net/c# based) it required synonyms pointing back to persistentdb so it could finish processing the policies - process could only deal with 1 db (per intrinsic design)

    main issue with MS design is that circular references should only be considered at object level, not project level, specially taking in consideration that in many cases the objects only need to be resolved at execution time.

  • Phil Parkin

    SSC Guru

    Points: 244669

    Thanks for taking the time to write that out.

    While I'm sure it would have been helpful in this case to permit 'circular' references, I'm still not convinced that this is a sound design.

    I'm happy to have one-way links (eg, to a Utilities database), but two-way links, where each DB depends on the other in some way, just feels wrong to me. I've always been able to avoid this, or engineer my way out of it. My ETL processes always head upstream only.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply