Attribute relationships are essential to the structure of a logical data model as they are the key to joining data from different attributes and aggregating fact data to different levels. Attribute relationships describe logical associations between attributes that exist based on business rules.
Simple & most frequently used examples is shown below:
Year –> Quarter –> Month –> Week –> Date
The above time hierarchy shows the relationship that exists between each of them and hence its easy for the end user to define the attribute get it created with the necessary relationships between them. Like say,
Year ( One to Many ) Quarter ==> Eg: ( 2012 –> 2012Q1, 2012Q2, 2012Q3, 2012Q4 )
Quarter ( One to Many ) Month ==> Eg: ( 2012Q1 –> Jan/Feb/Mar )
Month ( One to Many ) Weeks ==> Eg: ( Jan –> Week1/Week2/Week3/Week4 )
Similarly with the Date attribute. So based on the above created relationship, the end user can drill from any level of data to other ( Slice / Dice could be done – Drill up/Down)
Attributes are related to each other in one of the following ways:
– Direct—A parent-child relationship exists between two or more attributes. In the data warehouse, direct relationships are explicitly defined using lookup tables or distinct relationship tables.
– Indirect—Two or more attributes are related only through a fact or set of facts. In the data warehouse, indirect relationships are stored only in fact tables.
For example, Month and Date are attributes that have a direct relationship. Each month is directly related to specific dates. However, Customer and Date are attributes that have an indirect relationship. Without facts, these attributes are not inherently related. However, if customers make purchases on specific dates, you can relate these two attributes through the facts that capture those sales.
In a logical data model, determining the direct relationships that exist between attributes helps you identify logical groupings of attributes. Every direct relationship between attributes has two parts—a parent and a child. A child attribute always has at least one parent, and a parent attribute can have multiple child attributes. The parent attribute is at a higher level than the child. For example, in a relationship between Month and Date, Month is the parent attribute, and Date is the child attribute.
You determine the type of relationship that exists between directly related attributes by looking at the elements of each attribute. Directly related attributes have one of the following types of relationships:
–> One-to-one—Each element of the parent attribute corresponds to only one element for the child attribute, and each element of the child attribute corresponds to only one element for the parent attribute. The relationship between Citizen and Taxpayer ID attributes is an example of a one-to-one relationship. A citizen can have only one taxpayer ID, and a taxpayer ID is associated to only one citizen.
–> One-to-many—Each element of the parent attribute can correspond to one or more elements for the child attribute, but each element of the child attribute corresponds to only one element for the parent attribute. This relationship is the most common type that occurs between attributes. The relationship between Month and Date attributes is an example of a one-to-many relationship. One month contains many dates, but each date can occur only in one month.
–> Many-to-many—Each element of the parent attribute can correspond to one or more elements for the child attribute, and each element of the child attribute can correspond to one or more elements for the parent attribute. The relationship between Customer and Account attributes is an example of a many-to-many relationship. One customer can have many accounts, and each account can be associated with multiple customers (for example, a joint checking account).
( its not advised to use m-m relationship in the project, rather try to bring them down in the table itself to 1-many relationships – will discuss later if possible on how to overcome m-m relationship in a project )
