Re: Using tables in other PostGreSQL database

Pettis, Barry wrote:
>> It sounds to me like you want to share a single database between
> users,
>> possibly using a suitable mix of schemas and roles to apply suitable
>> permissions. If you don't want them to have shared access to the data
>> then you can have separate databases and grant them access only to
> their
>> own DB.
>
> Ok this kind of hits my issue right on the head. One of my tasks is to
> generate a summary report of metrics from the local site here. Now each
> week I need to summarize information on the product that was shipped
> that week. My IS dept exports a text report each week which contains (
> some of the information on the shipped product ). I know that another
> individual uses this information for reports that he has to do. So
> right now he is parsing this data back out from the report and storing
> the info in a PostGre table.

aside: PostgreSQL or Postgres, rather than postgre. Or "pg" if you
really need to keep things short.

The fact that you are taking a report (produced from a database, one
suspects) and re-inserting it to another database already means you're
doing copying here, so it' probably not worth worrying over excessively.

> You said the point of a database was
> coherent set of connected facts. Now his use of the report is to report
> to his supervisor what and how product for his business group the
> factory has made and shipped each week. Now the raw report is "ALL"
> devices. Because he is extracting it he stores the entire thing not
> just his small section of it.

OK, we have user1 needs all_products_summary. This isn't actually live
data, it's a copy of a regular snapshot.

> Now like I said I'm being tasked to report for the mfg division on how
> much, how fast, and other metrics which begins with data in his
> database. Now the "coherent facts" thing would state that I shouldn't
> put a mfg report info into the business group ( marketing ) groups
> database. However, he has this table in his database. I want to use
> it.

You want to use mfg_products_summary which is a subset of the above.
It's going to link to some manufacturing-specific data that marketing
probably don't want.

> Now for me I'd store this information in it's own database and give
> everybody restricted access. This way if they need it they can get it
> and they can use it. But as of right now I can't tell one database to
> look in another database and use a table that it finds there. I think
> that kind of restricts the use of data, or it promotes the duplication
> of data being stored.

You'd like shared access to a centralised body of data, which sounds to
me like a single database.

> I can't answer to "foreign-keys" or databases on different servers
> etc... due to my lack of ( sufficient { will that work } ) knowledge.
> So maybe as my experience grows things will become clearer.

Well, not sure about clearer. More complicated certainly :-)

> It's just right now I had to make my own data loader and store this data
> that I know is being done by another... From a business productivity
> point of view I see this as a waste of money.

Sounds like you want a single database (let's call it "reporting") with
separate schemas such as:
- manufacturing
- marketing
- is_dept_weekly
- shared
You place the relevant tables, views, functions etc in the relevant
schemas and then you can control who has access to what parts of the
database. That also lets you share useful views etc.

You'll have at least three db user accounts: you, marketing, db owner.

Restrictions:
- all the data will be in the same character set and locale

--
Richard Huxton
Archonet Ltd