link 2 databases (1 Viewer)

[Misiek]

Hello all,

We have one database with table Incidents and table Task, every task must be assigned an incident to it (one-many).

We will also have another database with table task. Those are singular records, not related to anything, just task and due date, completion date.

Yesterday, my boss asked if we can see outstanding tasks form first database in the second one, but we don't want to see tasks from second database in first one.

Here is the question:
Do I just link table from first database to second one, I will see all records and they will update no matter which front end we use?
Or do I rather have to create a separate table in second database and just pull records to it from first one.
How would it work if record is updated in either, how would they sync?


Can you please verify if my thinking is correct or point me in the right direction?
Thank you

 

[Ranman256]

yes, link the 1 table into the Incedents db.
then join the 2 tables.

Look into an OUTER JOIN for the query to get what you need.

 

[Misiek]

Your reply makes no sense.

Another solution:
Create a separate front end for the existing TASK table, and add records unrelated to table INCIDENTS... Would it work?

 

[plog]

Do I just link table from first database to second one, I will see all records and they will update no matter which front end we use?

If I understand you correctly then yes.

I believe you are ultimately asking how linked tables work and you are correct in your understanding. A linked table is like a shortcut to MS Word on your desktop. Your desktop doesn't contain a whole new instance of MS Word, it simply redirects to the one instance you have and runs that program. Same thing with linked tables, it just redirects the action to the actual database where the actual table resides.

You can easily test this. Create a real table in Database1 and a linked table to it in Database2. Add some data in Database2, close out completely, open Database1 and your record will be in the actual table.

 

[jdraw]

Misiek

What exactly does this mean?

Your reply makes no sense.

Do you not understand what ranman said?

Perhaps I'm not understanding your set up, but it seems you have some sort of need to do Task Management/Incident Management- and you have multiple databases involved.
From a design aspect it appears to be an issue of scope and/or analysis, since in many cases, you would have 1 database and multiple tables and relationships based on business needs, but readers do not know anything of your operational environment.

If readers knew more details of your Task/Incident management in business terms, I'm sure more focused responses would follow.

 

[Misiek]

Please see current database relationship image

The new (potential) database will require the following fields:

ID-PK
EntryDate
DueDate
ComplDate
User1FK
User2FK
Description
AreaFK
PriorityFK

However, I don't want those new tasks to link to the table T_qa.
In the new database I want to see the old and new tasks (from both databases) I need to be able to enter completion date. Also I need to be able to add task to the new database but the task won't appear in the old (existing) database. The existing (old) database will still be used for for different tasks.

Basically the new database will show me records from both, but can only add to the new one.

The question is: HOW TO?

Thank you.

 

[plog]

The relationship image you provided is incorrect. The big rule you broke is that there should only be one path between tables. Between T_qa and T_department I can draw 5.

T_qa should not be linked indirectly and directly to all those tables. I don't know your data, but I know relationships and what you have set up is incorrect.

You need to do a wholesale clean. Start your relationships from scratch. Then add tables 1 by 1. Start with your main table first (which is probably T_qa). Then add what you think is the second most important table (probably T_task) and ask yourself, is T_task really related to T_qa or does it need to go through another table to get there. You do that for all your tables one by one and you will set up your relationships properly. The key is always asking 'How does this table fit in?'. What specific table is it in a relationship with.

 

[Misiek]

Plog,
If I change this it will mess up with all existing records.

Anyway. What shall I do with the new TASKs?

 

[JLCantara]

@Plog:

The big rule you broke is that there should only be one path between tables. Between T_qa and T_department I can draw 5.

Any reference about this one? (Not the it's well known kind of stuff)

@Misiek:

Why do you need to have 2 DBs?
What is DB #1 and DB #2, they have names don't they...
T_qa has several useless fields: attach the full screenshot of your DB.

Your new table is not normalized, etc.

 

[plog]

Any reference about this one?

https://en.wikipedia.org/wiki/Database_schema

It's a practical implementation of ensuring data integrity and eliminating data redundancy among your tables.

 

[jdraw]

misiek,

As per my earlier post:

If readers knew more details of your Task/Incident management in business terms, I'm sure more focused responses would follow. Can you tell us about what you are trying to do in business terms -- no Access, no jargon?

 

[plog]

If I change this it will mess up with all existing records.

No it won't. I'm not advocating any changes in the data itself (of course that's mostly because I don't understand your data). I'm saying the way your tables relate need to change. This can be done without changing any records. There should only be one path between 2 tables, not the 5 you currently have.

 

[Misiek]

here is ful view

if we have any quality problems, we report it to the T_QA table, then to prevent this happening in the future we create a TASKs, which are always related to 1 record in the QA table.
Current layout been developed with help of Gina, on this forum. Therefore I strongly believe it's corect even if it does't lok so.

The existing database, doesn't allow to enter a TASK which isnt related to a QA record.

This is why, (my boss) wants another database where we can have TASKs for our departments, not related to quality, but want to see the quality tasks in the new database.
That is why I asked, shall I create a separate database for other tasks, and then link the old table TASK to the new DB, or somehow modify the existing table TASK to allow addition of new not related TASKs.

I hope this is clear.
Michael

 

[JLCantara]

@RanMan #2: I'm sure there is a typo: you most certainly meant Indecent db.

@Plog #10: I check the reference and found no mention of your dogma; to me, case closed...

Create a NEW table in your db (ex. ThisIsTheNonLinkedTask_Table?). You have circular relations: that's a major design flaw. Do you really want to be help or just want us tell you how to handle a poor schema???

 

[plog]

Me:

The big rule you broke is that there should only be one path between tables

JL:

@Plog #10: I check the reference and found no mention of your dogma; to me, case closed...

...You have circular relations: that's a major design flaw.

You do realize that circular relations is logically equivalent to having multiple paths between tables? So do you have issue with my ideas or my semantics?

 

[Misiek]

Guys, lets just drop it who's more right thing..

If I create separate table in the existing DB, can link it directly to T_area & T_equipment?

 

[plog]

Guys, lets just drop it who's more right thing..

Never. Pedantic arguments to inflate my self worth are the only reason I use the internet. That and to make non-sequitor racist, homophobic and anti-semitic comments on cat videos posted by 13 year old girls on youtube.

I will however take time from that to say that sure, you can add a new table linking T_area to T_equipment but you'll just be adding to the problem that already exists: multiple paths among tables.

Hopefully my inflammatory bolding will inspire a new round of pedantic arguments.

 

[Misiek]

what if instead of creating another table, I would modify my current table T_Task, so user can add tasks without relating it to T_qa?

 

[plog]

Actually, that's the one relationship T_qa should probably have.

I don't think T_qa should be directly linked to all those other tables, but instead should obtain that data in those tables by going through the tasks table (and then the others).

For all those links coming off T_qa, you need to ask 'Does this data really belong to this record in T_qa or does it belong to some other table that T_qa really is related to?'

For example, Does a user really belong to a record in T_qa or does a user belong to a task which in turns belongs to a record in T_qa?

You haven't really explained the entirety of your process, so nobody here can really help you. But we all agree its not both ways--not directly and not via Tasks. Hopefully asking all those questions about what belongs to what will help you decide what relations should remain, but if not, explain the system this database models and we can provide guidance. As of right now, I think T_qa should only be related to T_tasks.

 

[Misiek]

This is how it works:

User enters data into T_qa
Each record in this table must have the following:
-UserID (to track who reported it)
-Location: T_department, T_area, T_equipment (to track where it happened)
-all other tables on right hand side of the relationship image posted previously, to help to filtering and tracking

Records in table T_qa can but don’t have to have:
-T_task (some incidents reported to T_qa are minor and don’t require any tasks afterward)

Also
T_task must have userID, in most cases this userID will be different to userID who reported the incident in first place into table T_qa.



The new task table or database must have:
-userID1 (main owner of the task)
-userID2 (second owner if first one not available)
-location (T_department, T_area, T_equipment)
-Other new not yet exiting tables

Hope this helps