ER Modelling?

[Roo]

Hey guys, dunno if any of you are any good with basic Entity Relationship modelling but Im a little stuck on something.

I need to model this example basically,

An employee can work on many projects but only one project per city. An employee can have many skills but can only use a given set of skills on a single project.

So I was thinking of using an 'assigned', and doing somet like this,

employee
|
|
skill ----------- assigned ---------- project
|
|
city

So it would read, that an employee is assigned to a project within a city and uses a set of skills within that city? But I dont think thats right lol, plus how do I show that an employee can only work on 1 project per city and use only a given set of skills within 1 project?

Please help, asap would be good lol.

[blacksheep]

its just cardinality - you mark the bounds on the diagram ie 1..* would be one to many or 1 would mean 1 and only one

[tam9]

There's 3 kinds, 1 to 1, 1 to many and many to many. It's a hard subject to grasp but once grasped you have just entered a new realm of power. Relational Databases are great and with the likes of MySQL they are getting better and better..but lets hope not too good or we'll start to be out of jobs soon enough

[Roo]

Cheers for the replies guys, I know what you mean, but its much more difficult then that. This is known as a 'complex relationship'.

Its not as simple as 1 to many, etc as there can be 1 employee to many projects but only one employee to a city. But there can also be many projects to a city, if you see what I mean?

Any other ideas?

[tam9]

Yes, thats many to many relation ships mate...right this guy joe who stays in manchester has the projects of sales, advertising and publishing

and now we have that prick pete who joe hates also heading up a few projects in manchester but pete is fortunate enough to stay in preston and manage the projects from there. So in effect we have pete and joe from different parts of the country managing effectively the same projects or diffetrent projects in the 1 city.

My method for creating simple db's is to do diagrams...and with larger scale db's.

Does that make sense?

[RhinoBanga]

In these circumstances I use cross-reference tables to determine the relationship, e.g.:

employee (parent)
emp_no


project (parent)
project_no


skill (parent)
skill_no


city (parent)
city_no


project_employee_xref (child)
project_no
emp_no


skill_employee_xref (child)
skill_no
emp_no


city_project_xref (child)
city_no
project_no


etc
etc



The actual limitions/business rules, e.g. 1:1 or 1:*, is handled by triggers.

[Roo]

In these circumstances I use cross-reference tables to determine the relationship, e.g.:

employee (parent)
emp_no


project (parent)
project_no


skill (parent)
skill_no


city (parent)
city_no


project_employee_xref (child)
project_no
emp_no


skill_employee_xref (child)
skill_no
emp_no


city_project_xref (child)
city_no
project_no


etc
etc



The actual limitions/business rules, e.g. 1:1 or 1:*, is handled by triggers.

So how would I model that then?

Tam9, yes I understand. Didnt mean to sound patronizing or anything mate.

[TwoPlAnKs]

figure out what you want the database to be in your head, it'll take about 30 seconds, make sure it works and then you can draw the diagram from that