March 14, 2008 at 1:18 pm
Hi all,
I am debating if I should go ahead with my instincts which are telling me that I shouldn't name 2 tables with the same name... or if I should go ahead and name more then one table the same name as long as they are in different schema...
something like (making stuff up here for sake of showing an example...)
car.Reservations
hotel.Reservations
flights.Reservations
or
audit.TransactionTypes
inventory.TransactionTypes
sales.TransactionTypes
etc...
should I go this way or use something more traditional like dbo.SALES_TransactionTypes or dbo.SalesTransactionTypes?
March 17, 2008 at 7:36 am
You're going to hate this answer... It depends.
I think it really depends on the definitions of the tables. I also think different people are going to have different approaches. For example, I have done exactly this. We have transitory data and permanent data that we're keeping in a single database. The temporary data is stored in a schema called Inprocess and the permanent data is stored in a schema called Billed. The Inprocess data can be inserted, deleted & updated. The Billed data can only ever be inserted. The structures and table names are exactly the same between the two schemas.
However, I don't think I would name a Car.Reservation and a Hotel.Reservation because in my mind you've just defined an object called Reservation. If it's not the same between the two schema's, you could be creating confusion. I'd go with Car.CarReservation and Hotel.HotelReservation as redudant as that looks.
Helpful at all or just more mud?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2008 at 8:08 am
thanks for your input, what you've done make sense
I also posted on dbforums.com,
http://www.dbforums.com/showthread.php?t=1628324&page=1
feel free to read what people there have said, seems I am getting a wide range of answers for that one 🙂
March 17, 2008 at 8:17 am
Yeah, that's a lively set of responses.
One thing that did come up over there that I should have raised, what are you using the schema's for? Is it to seperate ownership in order to create a more granular security model or are you just categorizing? If it's the latter, you might want to consider not doing that and instead go with a naming convention.
Should I toss in some expletives, just to fit in over there?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2008 at 8:56 am
although I got 10x more answers on dbforums.com then here your posts seem to be the most straightforward 🙂
Basically my main intention was to categorize my database objects, a bit like if the schemas were "namespaces"... The added benefit of being able to customize security by schemas is great too but it wasn't my main goal.
But I guess you are right, I will use prefixes and leave everything under dbo. I will consider using schemas if I need to implement granular security
March 17, 2008 at 9:03 am
You might wait a bit and see if anyone else posts anything else interesting here. You are working in an area where there is room for interpretation and others might come up with some difference views that are useful to you (and maybe me).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2008 at 1:11 pm
If you are using the schemas for the purpose of securing the data in one table vs. the other and only giving users access to one of them, using the same name can be great. Also, you can do this to set a context for an application and use your connection information to allow you to re-use code.
For example, you may have a "Contacts" table in your database that you want to be different depending on user permissions. In this case, you can use your default schema to allow you to simply query the Contacts table in the database and it is magically managed for you.
I would say this can be done and done well, but is a bit risky in a database as it tends to be confusing. Just from a developer standpoint, opening the wrong table and looking at the data when troubleshooting an issue seems like an easy thing to do. So - if you do this, I would document really well.
Now, on to having two different items named the same thing. I would not do that. SalesOrders.Items and Clothing.Items (presumably with different columns) looks organizationally nice, but is probably going to be more trouble than it is worth. It is not really the purpose of schemas to allow you to name lots of different things the same thing. Remember that it is in the security section in management studio for a reason.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply