April 25, 2008 at 7:52 am
I'm creating a database(s) that will store our employees information. From personal information, hardware they have checked out to surveys HR requires them to take. I would like to keep their personal information private and locked down, while surveys and hardware would be a little more public.
My initial thought would be to have a database containing their personal private information. Then having a database for the other information that would link back to the private data to retrieve their name via their employee id assigned in the private database.
Is this something practiced or do people usually just put it all into 1 database?
April 25, 2008 at 8:59 am
Personally, I would have two schema's within the same database. This will allow you to lock down one schema, but have referential integrity between the schemas where as you would sacrifice that if you used two databases.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 25, 2008 at 9:10 am
I agree with Grant.
How do you plan to restrict access to the personal information if you have a link from the non-personal information?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 1:23 pm
Just when I think I'm starting to get good at this stuff, I come on here and realize I'm not, haha. Schema's, hmm...where do I begin? Would this schema be for user permissions on each database (feel free to elaborate on this)? Currently, I'm the only dba (more of an application developer) here but we have the intentions of hiring more in the future. I'm thinking that an employees information such as salary should not be exposed to very many people, if any (including multiple dbas). How is this handled elsewhere? Should this data be encrypted\decrypted at the application level. I'd assume this would complicate things when you migrate to a new application (unless its using the same encryption). I think decrypting this information would slow reporting down and create an issue when using a 3rd party software like crystal reports.
The reason I thought about doing it the way I did was because we would have several systems in place (some with large amounts of data, others not so much) with one thing in common, the employee. I could set up each one independently but this would mean that I would constantly be updating the employee table for each database when new ones are hired/fired.
April 25, 2008 at 5:46 pm
Schemas allow you to group objects and then assign permissions to users on that schema instead of each object. They replace the "owner" in 2000 with a true schema that isn't bound to a particular user.
If it's information you need to keep in one db, do that and use schemas. They are a little complicated and confusing at first, but you'll get used to them. If you go to two Dbs, then you complicate admin, DR, and more.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 28, 2008 at 2:59 am
Yes like others have said use 2 schemas in the same database. Having 2 diff databases would complicate things and also bring down performance .
"Keep Trying"
April 28, 2008 at 5:24 am
As to your initial question, it's two schema's in one database, not two databases.
As to your further explanation that you'll be bringing other applications online. As the other applications come online, evaluate their scope as applications. If warranted, seperate them into another database, but then you will have to deal with all the cross-database dependency issues. I wouldn't recommend storing Employee data in each db, but some duplication can be warranted. It really depends on the app, the amount of data we're talking about, whether you save time by duplicating data or by offering it up within a service... It gest complicated after that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 29, 2008 at 7:49 am
Ok, so its like setting up permissions in active directory. I can handle that. Where do I setup the schemas?
April 29, 2008 at 8:37 am
Hi, I'm in a similiar situation, you asked about the quantity of data that the other systems may generate to warrant having their own database, well in my database i intend to store data from an autoshop which has Spareparts,Workshop,Cashier,Invoicing,Accounts departments which are all inter-dependent and the workshop recieves about 200 cars a month, and the cashier enters approximately 200 transactions a month or more, Invoicing about 200 a month
April 29, 2008 at 8:57 am
go into the Database, select the Security folder, there's a Schema folder under there. You can create, set an owner, grant rights to a role (NOT users), etc.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 29, 2008 at 9:02 am
One more thing, if you are accessing lots of data from other databases because there are separate apps, then you can combine those into one database using schemas IF your application can handle that. you'd need to test and think about whether that makes sense.
If you want to/need to keep them separate, you have two choices. One is that you can set up views in each database that point to another database, which allows you some abstraction and you can access the view like a table. then if you needed to duplicate the information, replace the view with a real table and you're ok.
The second way is to replicate the shared data to other databases. this works good if it's read-only data. If it's not, then merge replication works, but it gets more complicated quickly.
Both of these could allow you to separate databases to separate instances if performance dictated it, but linked servers connecting views in #1 is more complex with what seems like worse performance.
It's a complex issue and I'm not sure that there is a good set of guidelines. This is one area where the DBA has to weight the pros and cons and decide what seems best in that situation.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 29, 2008 at 9:17 am
codevault33 (4/29/2008)
Hi, I'm in a similiar situation, you asked about the quantity of data that the other systems may generate to warrant having their own database, well in my database i intend to store data from an autoshop which has Spareparts,Workshop,Cashier,Invoicing,Accounts departments which are all inter-dependent and the workshop recieves about 200 cars a month, and the cashier enters approximately 200 transactions a month or more, Invoicing about 200 a month
Not knowing all the details, please take this with a HUGE grain of salt...
It doesn't sound like you're talking about a very high volume system. Because of the interdependency of the data, I'd leave it in a single DB based on what you've provided here. Different details could change that point of view.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 29, 2008 at 9:43 am
I agree with Grant, 1 Db with different schemas for the amount of data you are looking at.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 29, 2008 at 9:52 am
Hi, thanks very much for the advice, keeping them in one database will make it much easier to implement the features i want, please can you give me an idea of how high a volume of data would warrant separate databases. AND you're right i didn't give all the details about the system i thought it would be out of scope, below is the description of the system, Thanks:
Description
-----------
The project that can be used to manage an auto sales and repair shop
1. Car sales (Used/New)
2. Spare Parts sales (New/Used)
3. Car Repairs
4. Cashier
5. Accounts
RQUIREMENTS
-------
1. Car Sales
---------------
i. Keep track of cars in stock
ii. Keep Track of cars sold
iii. Create invoices
iv. Generate report of cars sold by car type or date
v. keep track of customers
2. Spart Parts sales
---------------
i. Keep track of Inventory
ii. Keep Track of parts sold
iii. Create invoices
iv. Generate report of parts sold by type or date
v. keep track of customers
3. Car Repairs
---------------
i. Keep track of cars
ii. keep track of customers
iii. Create estimates,job orders,invoices
iv. Generate report of cars recieved and cars released
4. Cashier
---------------
i. Keep track of payments and deposits
iii. Create recipts
iv. Generate report of payments and deposits
5. Accounts
---------------
i. keep track of paid and unpaid customers
iv. Generate report
BUSINESS PROCESS
----------------
1.Car Sales
------------
When a car is/are bought an invoice is raised and the quantity of the cars are updated in the stock record, the invoice is sent to Accounts to record the invoice and the amount in the customers account , the customer is then given the invoice, he then goes to the Cashier to pay, the cashier records the invoice number and customers name and the payment, the cashier then sends the payment details to Accounts to update the customer's account.
1.Spare parts Sales
------------
When a parts is/are bought a store requisition debit note is raised and sent to the manager for approval, then an invoice is raised and the quantity of the parts are updated in the inventory record, the invoice is sent to Accounts to record the invoice and the amount in the customers account , the customer is then given the invoice, he then goes to the Cashier to pay, the cashier records the invoice number and customers name and the payment, the cashier then sends the payment details to Accounts to update the customer's account.
1.Car Repairs
------------
When a car is receieved for repairs the customer and car details are stored in a database, then an estimate is raised, the estimate will be given to the customer, if he approves then a job order is created based on the estimate, the job order is then sent to the spare parts.....after the parts are recieved and the car is fixed, and invoice is created based on the job order, the invoice is sent to Accounts to record the details in the customer's account then the customer is given the invoice to pay at the cashier, the cashier then sends the payment details to Accounts to update the customer's account.
All this to happen on-line.
Thanks
April 29, 2008 at 11:22 am
Short answer: it depends.
Slight longer answer: When looking at a system like this, you have to decide, is it a single integrated system or a series of apps that have shared resources. If it's the latter, then which resources are so dependent on one another that they must remain in the same database and which resources are independent enough and have some other need for a seperate database (high transaction volume, high reporting volume, different development team or development time, stuff like that). Once you've gone through that process, then you've split up the databases where appropriate. It's not mathematical, but quite organic.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply