-
I need help with relanshionships
I am new in all these stuff.
I have create 7 different tables.
Office table
Machine table
Physicist table
Physicians table
Engineers table
Licenses table
Officers table
I need to answer these kind of questions:
Who are the physicists at X office?
Who are the engineers at X office?
When does the license due at X office?
How can I do that?
Am I ok with the tables?
How can I create the relationships?
Thanks and I hope soembody can help me.
Leandro.
-
The tables are ok, you can create additional tables to store the relationship, for example, create a table office_physicist, the main two fields would be office_id and physicist_id.
To make the whole structure simpler, you may want to use a table "people" to replace those staff tables, specify their role with a field, for example:
role_id 1 -> Physicist
role_id 2 -> Physicians
....
It's easier to maintain this way.
-
That is a good idea.
But if a person plays two different roles at the same time, how can I do that?
should I repeat the row with the same name, telephone but a different role?
Ej: John is a physicist in X location, but is also an officer in Y location.
How can do that?
The database is very simple (I think so). I have 70 different offices, each has a license, and in each license I have a list of physicist, physicians, engineers and officers. Each of these guys can be listed under different licenses. So sometimes I need to know who are the guys that are listed under X license or office.
Am I clear?
Somebody can help me with the design?
Thanks in advance.
-
Then change it a little bit:
Make the office and people tables simple, store all relationships in a relationship table.
The basic roles like this:
[office]
id (PK)
name
...
[people]
id (PK)
name
....
[role]
id (PK)
name
...
[relationship]
office_id
people_id
role_id
PK is (office_id, people_id, role_id)
Try some queries and evaluate if it is easy and efficient.
-
OK. I understood the logic of the 3 tables. The thing is, that I do not understand what data should I put in the relationship table. Do I have to fill that table? The other 3 are filled with the data.
Thanks very much for your help.
-
Yes, you should fill that table, otherwise who will do it for you?
One common situation:
There is a new engineer, you need to insert a new record into the table "people", then insert a record into the table "relationship". So the record should be:
people_id: this value is from the table "people".
role_id: he is an engineer, in the role table, you have all roles with an ID.
office_id: you know which office he is assigned.
-
and what happened when that engineer is assigned wiht an area with many offices? Do I have to fill as many records in the relationship table as offices is he assigned? I ask that because that is very common in my department.
Thank you so much.
-
Yes, insert one record for each relationship, that's why the primary key is the combination of office_id, people_id and role_id.
-
thank you so very much, I will try right now.
-
One question:
I have 5 different roles, 70 different offices and 150 different people. Do I have to make all the posible combinations and put that data in the relationship table?
Ex: Relationship Table
Office ID/ Personal ID / Role ID
1 / 45 / 2
1 / 34 / 1
1 / 36 / 1
etc....
Is that correct? If so, I have a lot of data to put in.....
-
Yes, if the database doesn't have all the relationships, how could you read all relationships? Any database programs work this way.
-
So bassically I have to write all the information that then I will need to get.
It is like I do all the job and then the only thing I can do is to get the information in different ways. Is that correct? I though that one I put in the tables, the names of the persons , the facilities and the roles, then I could ask the database who works for X facillity, etc. Instead of that, I have to tell the database: For X facility, we have ABCD people, who are all engineers, and GHK who are physicist. And then I can ask who are the physicist at X facility?
Is that correct? Sorry to bother you but I am getting a little confuse.
thanks a lot.
-
Yes, that's right. You can make a form so that anyone can input these data. Normally developers don't have to do it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules