Creating database tables - use Umbraco database or different database?
Hello,
I'm building an application that uses Umbraco v7. I want to build a user table in our database to manage our application's users (signup, login, roles, etc.). What is recommended? That I create the table in the same database that Umbraco uses, or a completely different database?
"What is the advantage to move a new table to a separate database? Think about it."
Separation of concerns.
I just have no idea what Umbraco does with its database. If I add a new table, will it detect it and try to do something with it? And what about the Umbraco migration table? Can I use it to keep track of table migrations that are unrelated to Umbraco tables? How 'bout seeding the database in new environments. Would I have to seed the Umbraco tables or would that be done separately with a fresh install of Umbraco?
All these questions make me uncertain about what the unintended consequences will be of mixing Umbraco tables with other customized tables, especially given that I have no idea what Umbraco does with the database.
Firstly, Umbraco comes with a concept of 'Membership' and 'Umbraco Members' out of the box - Members can be organised into Membership Groups and you can use the UI of the backoffice, to choose which parts of your Umbraco front end site are 'protected' (right click a content item and choose Public Access option to get the gist).
It uses a .net MembershipProvider, and this is replaceable with your own extended implementation if needs be. (There is also a plugin that enables you to use OWIN, Asp.Net Identity - https://github.com/Shazwazza/UmbracoIdentity)
Now it may be that this gives you enough!, or at least a starting point for you to build something to manage the front end access to your application.
But if you've considered this already, and ruled it out, and what to go it alone then there are some advantages and disadvantages to creating tables within the same database as Umbraco, and of course it completely depends on the nuances of your project!
Umbraco will not interfere with your custom database tables, it will only reference the tables 'it knows about', so there is nothing to fear here... but if your custom database table, will be a 'data lake' in size and have a gazillion entries, then the performance of this table on the front end, could in theory, hog SQL resources and therefore effect performance of the Umbraco backoffice, but in normal circumstances, such as a table to handle logins, this isn't usually a genuine concern!
There is an environmental factor to consider- eg if you intend to have separate staging/live environments and use a tool such as uSync Complete to move Umbraco content and Doc Types between environments (it can move Umbraco Members too I believe, it's ace), then uSync will not know about your custom database tables - so it depends on the nature of the content stored in the database whether it would make sense to be able to 'move it between environments' eg a 'products' database table might have that concept, but logins may not - having a seperate database table and WebAPI endpoint to expose the data, could mean it can be managed in one place and consumed in both environments...
But there are advantages for the tables being in the same database as Umbraco, you can, for example join on, your custom tables easily to Umbraco tables if you need to combine data.
And by installing a package such as Fluidity or UIOMatic - you can allow editors in the backoffice to update and insert into your custom tables.
I think it's possible to use an Umbraco Migration to run SQL so it would be possible, to use this mechanism to create your custom table on a different Umbraco environment: https://skrift.io/issues/umbraco-migrations-made-easy/ is a good article, and populate it with seed data.
So for me it hinges on how much of the content you are 'publishing' to the site in the custom tables, and how much you are 'writing' to the tables to track activity etc - and whether Umbraco Members gives you something you could work with out of the box.
Also if your login tables might be useful in other applications/websites for the same users - then that may also point to having a common login/account management app separated from the website, but if the logins are only relevant for this site, then separating may be overcomplicating it...
Sorry for the late reply. Been really busy, but I've finally come back to this question so I read your response.
Yes, it does answer my question. You mentioned that "Umbraco will not interfere with your custom database tables," which more or less settles the matter for me. If there won't be any unintended consequences of add my own custom tables to the same DB that Umbraco uses, then there isn't much of a point in going with a separate database.
I agree with Marcs great great answer and most of the time I put my tables in the same db as Umbraco.
I only see one scenario where it’s nice to keep them separate and that is if I would need to perform a major update of the Umbraco-instance. In this scenario it would be nice to be able to take a backup of the Umbraco-db, pull it down and work for a couple of days while the old site still runs and then just replace the Umbraco-db with the updated version without having to worry about any new users etc.
It’s probably “doable” but if you keep members etc in Umbraco any major upgrades will have to involve a very robust process/script if you need to perform it in the live environment.
It all depends on how much your “custom tables” needs to interact with data from Umbraco.
Creating database tables - use Umbraco database or different database?
Hello,
I'm building an application that uses Umbraco v7. I want to build a user table in our database to manage our application's users (signup, login, roles, etc.). What is recommended? That I create the table in the same database that Umbraco uses, or a completely different database?
Thanks.
Hi Gibran
It totally depends on your preferences. Do you plan to use this table only in the current database? then use the same database
What is the advantage to move a new table to a separate database? Think about it.
Thanks, Alex
"What is the advantage to move a new table to a separate database? Think about it."
Separation of concerns.
I just have no idea what Umbraco does with its database. If I add a new table, will it detect it and try to do something with it? And what about the Umbraco migration table? Can I use it to keep track of table migrations that are unrelated to Umbraco tables? How 'bout seeding the database in new environments. Would I have to seed the Umbraco tables or would that be done separately with a fresh install of Umbraco?
All these questions make me uncertain about what the unintended consequences will be of mixing Umbraco tables with other customized tables, especially given that I have no idea what Umbraco does with the database.
Anyway, thanks for your response.
Hi Gibran
Firstly, Umbraco comes with a concept of 'Membership' and 'Umbraco Members' out of the box - Members can be organised into Membership Groups and you can use the UI of the backoffice, to choose which parts of your Umbraco front end site are 'protected' (right click a content item and choose Public Access option to get the gist).
https://our.umbraco.com/Documentation/Getting-Started/Data/Members/index-v7
It uses a .net MembershipProvider, and this is replaceable with your own extended implementation if needs be. (There is also a plugin that enables you to use OWIN, Asp.Net Identity - https://github.com/Shazwazza/UmbracoIdentity)
Now it may be that this gives you enough!, or at least a starting point for you to build something to manage the front end access to your application.
But if you've considered this already, and ruled it out, and what to go it alone then there are some advantages and disadvantages to creating tables within the same database as Umbraco, and of course it completely depends on the nuances of your project!
Umbraco will not interfere with your custom database tables, it will only reference the tables 'it knows about', so there is nothing to fear here... but if your custom database table, will be a 'data lake' in size and have a gazillion entries, then the performance of this table on the front end, could in theory, hog SQL resources and therefore effect performance of the Umbraco backoffice, but in normal circumstances, such as a table to handle logins, this isn't usually a genuine concern!
There is an environmental factor to consider- eg if you intend to have separate staging/live environments and use a tool such as uSync Complete to move Umbraco content and Doc Types between environments (it can move Umbraco Members too I believe, it's ace), then uSync will not know about your custom database tables - so it depends on the nature of the content stored in the database whether it would make sense to be able to 'move it between environments' eg a 'products' database table might have that concept, but logins may not - having a seperate database table and WebAPI endpoint to expose the data, could mean it can be managed in one place and consumed in both environments...
But there are advantages for the tables being in the same database as Umbraco, you can, for example join on, your custom tables easily to Umbraco tables if you need to combine data.
And by installing a package such as Fluidity or UIOMatic - you can allow editors in the backoffice to update and insert into your custom tables.
I think it's possible to use an Umbraco Migration to run SQL so it would be possible, to use this mechanism to create your custom table on a different Umbraco environment: https://skrift.io/issues/umbraco-migrations-made-easy/ is a good article, and populate it with seed data.
So for me it hinges on how much of the content you are 'publishing' to the site in the custom tables, and how much you are 'writing' to the tables to track activity etc - and whether Umbraco Members gives you something you could work with out of the box.
Also if your login tables might be useful in other applications/websites for the same users - then that may also point to having a common login/account management app separated from the website, but if the logins are only relevant for this site, then separating may be overcomplicating it...
does that help illustrate the dilemma more?
regards
marc
Hi Marc,
Sorry for the late reply. Been really busy, but I've finally come back to this question so I read your response.
Yes, it does answer my question. You mentioned that "Umbraco will not interfere with your custom database tables," which more or less settles the matter for me. If there won't be any unintended consequences of add my own custom tables to the same DB that Umbraco uses, then there isn't much of a point in going with a separate database.
Thanks for the feedback both.
I agree with Marcs great great answer and most of the time I put my tables in the same db as Umbraco.
I only see one scenario where it’s nice to keep them separate and that is if I would need to perform a major update of the Umbraco-instance. In this scenario it would be nice to be able to take a backup of the Umbraco-db, pull it down and work for a couple of days while the old site still runs and then just replace the Umbraco-db with the updated version without having to worry about any new users etc.
It’s probably “doable” but if you keep members etc in Umbraco any major upgrades will have to involve a very robust process/script if you need to perform it in the live environment.
It all depends on how much your “custom tables” needs to interact with data from Umbraco.
is working on a reply...