Database Schema Question

Question

I am having problem understanding how merged schema works. If I have two tables from two discrete database systems

Company A work (work_ID, name, job_type, place, salary) Company B job (job_ID, name, work_type, net_Value)

How can I create a merged schema?

@sikas: Yes, I am trying to merge them together so that I can access the data from both — Lan
– Lan, Commented Mar 16, 2011 at 6:56
is there a mapping between the 2 tables? By mapping I mean, which attribute in CompanyA table maps to which attribute in CompanyB table. It will be helpful if you edit to answer to specify this mapping information. — Devendra D. Chavan
– Devendra D. Chavan, Commented Mar 16, 2011 at 8:15
one simple way is to create a many-to-many relation using a middle table, that is creating a table named, for example, work_job and the table can contain work_ID and job_ID which are linked back to work and job respectively. — sikas
– sikas, Commented Mar 16, 2011 at 17:51

schemathings · Accepted Answer · 2011-03-16 06:14:22Z

create view jobs as

select work_ID as job_ID, name, job_type as work_type, place, salary as net_Value from CompanyA.work A union all select job_ID, name, work_type, "Unknown Place", net_Value from CompanyB.job B

may need () around the contents of the selects depending on the db.

union all won't work like this as the number of columns provided in the two selects are different.

Collectives™ on Stack Overflow

Database Schema Question

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related