database-design
I am attempting to create a data model for a sports league
There are two entities in my domain. The first entity is a Manager and the second is a Team.
A Manager has
A Team has one Manager
If these are the design constraints, then how can we connect these two constraints?
The basic idea is to separate hierarchy of an organizational chart from actuals teams.
The Level table looks like
LevelID | LevelType
-------------------
1 League
2 Division
3 Conference
4 Team_Slot
Few more notes:
Alternate Key (PositionID, LevelID) is propagated into TeamPosition in order to allow a check constraint LevelID = 4 -- actual teams can fill-in only Team_Slots.
Team can not change Team_Slot within one season.
For simplicity, the hierarchy is here modelled with the levelled-adjacency-list; not most efficient for SQL hierarchies, but good enough for an explanation.
For more efficient hierarchy models, see Celko's book or just google 'SQL hierarchies'.
There are basically just two things here:
Your data model, which is the structure of your entities, attributes and relationships.
The graphical representation of your data model.
Your data model may be high level (entities/relationships only) or lower level (add in attributes with details of data types) and it may be logical or physical. It all depends on what stage of your design process you're at. The point of a data model is to record your database design, which is just an expression of your business requirements for persisting data.
Crows foot and ERD etc. are just graphical conventions for showing your data model pictorially. The great thing about standards is that there's so many to choose from, and this is also true of graphical conventions. Read this wiki article for a sampling.
Which conventions you choose may depend on what you need to communicate about your model, or it might just depend on what conventions make sense to you.
Don't get too wound up about all the different terminology. It's all just your database design and how you choose to record and communicate it.
Best Answer
The unique identifier for Manager would be the personnel id. That identifier would migrate to Team as a column and thus refer back to Manager. This will ensure each Team can be managed by one and only one Manager. This constraint would be a Foreign Key constraint. The constraint on Manager, which specifies the personnel id as the key, would be called a Primary Key constraint.