Hi there. I've done some Google searches on this, and the majority of things I found are for quite big and complex projects with potentially thousands of users. I want to ask for advice on my specific scenario, however, in which we will have at most like 30 users for an intranet ASP.NET website our employees will use for things ranging from an alert e-mail system to tracking and recording vacation days.
As it is now, we have one database called our intranet site's name. Inside this database, we have tables like AlertEmails, AlertEmailRecipients, ranging all the way to Employees, EmployeeGroups, EmployeeVacationDays, etc. It's ALL in this one database.
This is starting to become an eyesore for me. Our number of tables is growing, and it's starting to come to my attention that there is no logical grouping here whatsoever. It doesn't really make sense to have our Employees table in our database for the website because Employee information doesn't have anything to do with the website itself, the website is just a means to access it. If we decide to create some external program later, say an iPad application (just hypothetical here), to manage employees, it doesn't make much sense to have to access the Employees table through the website database. Does it work? Yeah, but it bothers me that it makes no logical sense.
What I WANT to do is create multiple smaller databases. For instance, a database called Inventory, a database called Employees, a database called Clients, and so on. Basically a database for everything that doesn't have anything to do with the site necessarily, and then in the intranet site database include only the tables relevant to the site itself, like the aspnet tables, users, and website calendar events, etc.
Logically, this makes sense, it becomes nice and clean and organized. Problem is, is it really worth creating an "AlertEmails" database, for example, when the AlertEmails function only has two tables?
I understand that both ways work just fine, I just want to create a system for our company that we won't regret years down the road when we possibly expand or whatever, and then we have like 100 tables in one database for the entire company, and the next new guy who comes in looks at it and goes "oh my god...this is horrible."
100 tables in a database is tiny; I have worked on a database with 1000s of tables in one database.
It is true that it can make sense to having different databases (or "schemas" within a database in Oracle terminology, with which I am most familiar) for different functional areas, like an HR database, a CRM database, a Payroll database, with some of them accessing data from the others. In an Oracle database these could be different schemas within a single database, which means that you can have referential integrity between them e.g. the Payroll.payments table having a foreign key to the HR.employees table, while still keeping them separate from a management perspective. If they were on different Oracle databases then normal referential integrity would not work and it would be complicated to deal with that.
In fact it probably isn't safe to answer your question without knowing which DBMS you are using.