Using sql tables with the same name on different schemas

Question

Hi,

Does anyone know of any white papers available that discuss the use of tables with the same name existing on different schemas?

I'm implementing on SQL Server but I imagine the theory can be applied to any RDBMS.

Thanks,

Gary

Answer 1

I'm not aware of any white papers on this issue, probably because its not a complex enough subject to warrant an entire whitepaper.

If you fully qualify your table names every time you use them (report.Customers or web.Customers instead of just Customers), then you shouldn't have any problems, even if you do have the same table name in more than one schema. You should be doing this anyway, because its a performance optimization (SQL doesn't have to work to resolve ambiguous names).

I would highly recommend not relying on the user's "default schema" to determine which table is being selected. That just leads to trouble when the same code is called by users that have different default schemas.

Answer 2

RDMSs will accept 'absolute' references to other tables and schemas in queries:

SELECT databasename.tablename.fieldname
FROM databasename.tablename
WHERE otherdatabase.othertable = ...

without any complaints. I don't know why someone would want to write up a white paper about this, as it'd be the equivalent of writing up a paper on how some people in different cities can have the same name ("Woah, did you know there's a John Smith in New York AND in Los Angeles?!?!?").

Answer 3

Ok, maybe I should make my question a little more specific. Say I have a db that serves two applications and there's some cross over of resources between applicatons but some datasets are stored on seperate schemas.

For example

App1.tblSomeData

App2.tblSomeData

What the advantages/disadvantages of using the same name for the table? The other alternative is...

App1.tblSomeDataForApp1

App2.tblSomeDataForApp2

This insures that all table names are unique, does this have advantages/disadvantages.

Thanks,

Gary