Обсуждение: app table names
My company has a handful of apps that we deploy in the websites we build. Recently a very old app needed to be included along side a newer app and there was a conflict w/ a duplicate table name needed to be used by both apps.
Thanks,
Jamie K.
We are now in the process of updating an old app and there will be some DB updates. I'm curious what people consider best practice (or how do you do it) to help ensure these name collisions don't happen.
I've looked at schema's but not sure if thats the right path we want to take. As the documentation prescribes, I don't want to "wire" a particular schema name into an application and if I add schema's to the user search path how would it know which table I was referring to if two schema's have the same table name. although, maybe I'm reading to much into this.
Any insights or words of wisdom would be greatly appreciated!
Thanks,
Jamie K.
On Tue, Mar 16, 2010 at 1:03 PM, Jamie Kahgee <jamie.kahgee@gmail.com> wrote: > My company has a handful of apps that we deploy in the websites we build. > Recently a very old app needed to be included along side a newer app and > there was a conflict w/ a duplicate table name needed to be used by both > apps. > We are now in the process of updating an old app and there will be some DB > updates. I'm curious what people consider best practice (or how do you do > it) to help ensure these name collisions don't happen. > I've looked at schema's but not sure if thats the right path we want to > take. As the documentation prescribes, I don't want to "wire" a particular > schema name into an application and if I add schema's to the user search > path how would it know which table I was referring to if two schema's have > the same table name. although, maybe I'm reading to much into this. > Any insights or words of wisdom would be greatly appreciated! When you have two tables with the same name in different schemas, the one that matches the firs schema in the search path wins. I would definitely use schemas to separate out apps here. If it doesn't need to see a schema, then leave it out of that user's (app as a user that is) search path.
On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee <jamie.kahgee@gmail.com> wrote: > I'm curious what people consider best practice (or how do you do it) to > help ensure these name collisions don't happen. Do not mix data from multiple applications in one database. Use multiple databases to isolate them entirely.
On 3/16/2010 3:35 PM, Vick Khera wrote: > On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee<jamie.kahgee@gmail.com> wrote: > >> I'm curious what people consider best practice (or how do you do it) to >> help ensure these name collisions don't happen. >> > Do not mix data from multiple applications in one database. Use > multiple databases to isolate them entirely. > > That's not always a practical solution to the problem, the Apps may need to share numerous tables, duplicating the data and keeping it sync can be a pain. Having to open numerous database connections to different databases is a resource hog . what i have been doing of late is defining PG_SCHEMA variable to tell the app where the data is located . Common tables to many apps go into the public schema or a schema that's in the search path. Selects look something like this "Select * from " + PG_SCHEMA + "foo" All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
On Tue, 2010-03-16 at 16:35 -0400, Vick Khera wrote: > On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee <jamie.kahgee@gmail.com> wrote: > > I'm curious what people consider best practice (or how do you do it) to > > help ensure these name collisions don't happen. > > Do not mix data from multiple applications in one database. Use > multiple databases to isolate them entirely. Or use Schemsa. Joshua D. Drake > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
On Tue, 2010-03-16 at 16:35 -0400, Vick Khera wrote: > On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee <jamie.kahgee@gmail.com> wrote: > > I'm curious what people consider best practice (or how do you do it) to > > help ensure these name collisions don't happen. > > Do not mix data from multiple applications in one database. Use > multiple databases to isolate them entirely. Or use Schemsa. Joshua D. Drake > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.
On Tue, Mar 16, 2010 at 5:25 PM, Justin Graf <justin@magwerks.com> wrote: >> Do not mix data from multiple applications in one database. Use >> multiple databases to isolate them entirely. >> >> > That's not always a practical solution to the problem, > > the Apps may need to share numerous tables, duplicating the data and > keeping it sync can be a pain. Having to open numerous database > connections to different databases is a resource hog . > That was not a consideration in the original post. In that case, make your application developers use unique names.