link 2 databases

[plog]

That's explanation of how your database works. I was looking for a database-free explanation of the real-world system/process it models.

I went back and reread your initial question and you mentioned these new tasks are unrelated to this database, but somehow need to be included. But with the structure you posted it does seem related to this database (T_area, T_equipment, etc.).

Why wouldn't the existing task table work?

Also, and back to your relationship issue, this new table wouldn't need to be related to T_department, T_area and T_equipment since those 3 tables are in relation to each other. You would be storing redundant data if you had all three of those pieces of information in that table.

Suppose you were making a database for professionl sports teams. You would have a League table, a Team table, and a Players table. Those 3 tables would not all be related. Players would relate to a team and teams would relate to a League. You could still find out that Wayne Rooney played in the Premier League, but that specific piece of information wouldn't be stored in the Players table (or the Leauge Table). You would have to associate Rooney with a Team and a Team to a League to get there, but you still could.

Your tables are breaking that idea. If a T_equipment is related to a T_area which is related to a T_department, then don't need to relate T_department to T_NewTasks. You simply relate a T_equipment to a T_NewTask and you can follow the path from T_equipment to T_area to T_department to find out what T_department the T_NEwTask relates to. You don't do it directly.

 

[Misiek]

Your tables are breaking that idea. If a T_equipment is related to a T_area which is related to a T_department, then don't need to relate T_department to T_NewTasks. You simply relate a T_equipment to a T_NewTask and you can follow the path from T_equipment to T_area to T_department to find out what T_department the T_NEwTask relates to. You don't do it directly.

Not really, as I mentioned in my previous post, user can enter a record into main table T_qa, without linking and creating record in table T_task. So how would user determine location (T_department, T_area)?

Therefore the existing TASK table wont work, as table doesnt allow user to enter new record without linking it to T_qa.

 

[plog]

Once equipment is assigned to Task, it can figure out the Department and area because T_equipment is linked to those tables.

You could make it so that linking it to a T_qa is optional

 

[Misiek]

ok then, but how?

 

[plog]

Via a query that links all the intermediate tables between the two.

 

[Misiek]

1. create query and add all tables? or only T_task and T_qa?
2. what do i do with it afterwards?
3. how serious this problem is (1-10)
4. if I dont do it, what would be next step in creating/editing new table for TASKs?

thanks

 

[plog]

1 &2 . It depends on what you want in the query. A query is a means to an end, not an end.

3. This could prevent you from adding data into your tables and omit some data/cause duplicates when you build queries/reports. One of many examples: T_qa is directly linked to T_user and T_qa is indirectly linked to T_user via T_Task. Its possible that T_qa.qaUserFK is different than T_task.tUserFK for the same record. Then what? Instead of T_qa being linked to one record in T_user its linked to 2 different ones. Which is the right user for the record in T_qa. That's the peril of multiple paths.

4. I don't know, muscle through it and hope you can get it to work. I really think it can go in the existing T_task table. Maybe your way will work, maybe not. But its never a good idea to proceed with a poor structure. You will experience errors in the future and have a pain of a time trying to understand them.

 

[Misiek]

Also, and back to your relationship issue, this new table wouldn't need to be related to T_department, T_area and T_equipment since those 3 tables are in relation to each other. You would be storing redundant data if you had all three of those pieces of information in that table.

Suppose you were making a database for professionl sports teams. You would have a League table, a Team table, and a Players table. Those 3 tables would not all be related. Players would relate to a team and teams would relate to a League. You could still find out that Wayne Rooney played in the Premier League, but that specific piece of information wouldn't be stored in the Players table (or the Leauge Table). You would have to associate Rooney with a Team and a Team to a League to get there, but you still could.

Your tables are breaking that idea. If a T_equipment is related to a T_area which is related to a T_department, then don't need to relate T_department to T_NewTasks. You simply relate a T_equipment to a T_NewTask and you can follow the path from T_equipment to T_area to T_department to find out what T_department the T_NEwTask relates to. You don't do it directly.

I am still trying to understand your suggestion. The reason I have this "double" relationship is I need cascading combo hence relationship between T_department - T_Area - T_equipment) and also need to store values of this location in table T_qa (hence relationship T_qa-T_department & T_qa-T_area & T_qa-T_equipment)

 

[plog]

You can achieve that without having it as a formal relationship in your relationships. You just make your cascading combo boxes working according to how it they relate.

 

[Misiek]

obviously, it's something I am not familiar with. Can you please step by step me?

 

[plog]

You can search this forum for instructions on cascading combo boxes, I havent done them in a while. My point is that you can still implement cascading drop downs without a formal relationship between tables that effect each other.

The real relationships is the indirect one via intermediate tables that lie between the two, but you still can implement cascading combo boxes limiting data based on another table even when those 2 aren't in a direct formal relationship.

 

[Misiek]

if I follow this, would I get where I supposed to be with my relationships?

https://www.youtube.com/watch?v=SpMyGlEInGs

But then my main table would store text i.e. Department, Area & equipment, instead of as currently numbers: 1,2,5, ...etc which I thought takes less space (for each record)

 

[plog]

I give up. Try it your way.

 

[Misiek]

Is this correct then?

thanks

 

[plog]

Closer, but no. There's still 2 paths between T_Task and T_qa. T_user should be linked to either T_Task or T_qa, but not both.

 

[Misiek]

1. I know, but until now my main table was T_qa to record incidents, and If I remove relationship between user and this table how will it work? Remember that not all records from T_qa will have assigned task to T_task, so how will my location work (department, area, equipment)??

2. Other problem, I need to be able to create a task without linking it to incidents.

3. at this moment if I open table department and click the '+' symbol to have a drop down it doesn't work or shows it other way around.

I just don't understand how this will all work in my front end, ... so lost :/

 

[jdraw]

You should NOT be doing data entry using Tables directly.

These 2 quotes are very confusing!!!

every task must be assigned an incident to it (one-many).

I need to be able to create a task without linking it to incidents.

I think you need to list in point form what your business (issue/opportunity) is.
You say you're lost. We don't know you or your environment or what you are trying to do.
Suggest readers need more info to be of help.