Help with an entity-relationship diagram re Manager entity type and its relationships
database-designerd
In the below ER diagram:
A manager can have many Users but a User can only have 1 Manager
A Manager must be able to assign multiple users to multiple courses
Are the manager relationships correct?
Best Answer
One issue with this design is allowing for a user to have more than one course at a time. A way to work this is to have a join table between the User table and the Courses table, with only three columns: Join_id, Course_id, User_id. The relationship between users and managers looks good to me, with many users being assigned only one manager each.
Regarding one of the comments; adding to the Course_Assigned table a few fields that include a session_id, start_date, and end_date could be used to directly connect a user to a particular session.
It's difficult to say if the ER is correct not knowing what is acceptable entered data. Based on your current design, including UserID in UserRecipes is unnecessary since you can get that information through RecipeID and joining it with the Recipes table and then using fkUserID.
But I think if Recipes table is supposed to be a dish like "Lasagna" and different users can submit their own lasagna recipes and you wish to capture that in UserRecipes, you need to probably move at least "Preptime", "Cooktime", "Ingredients", "Tools" and "Photos" to UserRecipes table. Then establish a many-to-many relationship between Recipes and Users in UserRecipes. Then remove fkUserID from Recipes and keep UserID in UserRecipes.
Just a side-note, I'd suggest keeping a more consistent naming convention for naming your foreign keys. In some cases, you are using CatID as a foreign key for CategoryID and then you're using fkUserID as a foreign key for UserID.
IMHO you just have to pay attention on the order of cardinalities, and you should note that Issue Date is not a weak key, but it's not a key at all: in fact you could have two account statements of different people issued on the same date, so you need a foreign key (Issue Date plus Number of Account)
Best Answer
One issue with this design is allowing for a user to have more than one course at a time. A way to work this is to have a join table between the User table and the Courses table, with only three columns: Join_id, Course_id, User_id. The relationship between users and managers looks good to me, with many users being assigned only one manager each. Regarding one of the comments; adding to the Course_Assigned table a few fields that include a session_id, start_date, and end_date could be used to directly connect a user to a particular session.