0

I have a question, with regards to how do you create the relationships between members of two different entities? For example, in case of 1:1 you can create a domain-based attribute that is referencing the entity. But in case you have a customer with multiple addresses, and you have an address entity.

When you update the member of the Address attribute of the Customer entity, based on the 1:M how would we be able to surface that relationship and connect the Address entity to the Customer entity?

So in case an Address member in the Customer entity changes, how would that update process work since we don't have a mapped relationship?

I know in M:M you would use a bridge table. And in the MDS database which table would contain that relationship info?

Any suggestions to articles and blog posts/Videos or ideas, would be greatly appreciated.

Thanks, Andrea

sheldonzy
  • 5,505
  • 9
  • 48
  • 86
user8760845
  • 1
  • 4
  • I'm not sure about articles or videos on the subject, but if you have a one-to-many relationship, that generally implies creating a separate table for this (assuming a customer can have unlimited addresses or you don't want to put a limit on the amount they can have). e.g. an `Addresses` table in your instance, with a link to the customer ID. If your customer can have a fixed maximum amount (e.g. 3 addresses max allowed), you can potentially keep it in one table if you prefer (Address1, Address2, Address3). It depends... – ZLK Oct 12 '17 at 21:23
  • You will need a Customer Addresses table, not too sure this should be maintained in MDS though. – RegBes Oct 17 '17 at 07:23

1 Answers1

1

In MDS (Master Data Services), you can use the Hierarchy feature.

- Option 1: create Explicit Hierarchy

If you're using SQL Server 2012 (possibly 2014 too), then an Explicit Hierarchy will solve this. (see: Explicit Hierarchies (Master Data Services) )

Basically, you will be able to use Customer entity rows as parent nodes in the hierarchy (think of a tree structure) and assign one or more Address entity rows as child of each Customer.

As the name suggests, this needs to be "explicitly" managed.. means either someone will do this manually (drag-drop) using the MDS Hierarchy UI page or you can use an SSIS package to automate it while loading the staging table. (see: Move Explicit Hierarchy Members by Using the Staging Process (Master Data Services) )

NOTE: Explicit Hierarchies have been Deprecated (starting SQL 2016 ...I think)

- Option 2: create Derived Hierarchy (bridge table- M:M)

This is similar to the bridge table concept that has been suggested earlier for M:M relationship, but in the MDS context.

  1. Create another entity, call it CustomerAddressRelationship (let's say), let's call it CAR for now.

  2. Add 2 Domain Attributes to CAR: one referring to the Customer entity, the other to the Address entity.

  3. Now, if you want, you can create a Derived Hierarchy on this CAR entity in the format CAR > Customer > Address. Read more about Derived Hierarchies: Many-to-Many (M2M) Relationships

- Option 3: create Derived Hierarchy (1:M)

  1. Don't create CAR. Just add a column CustomerID (let's say) to the Address entity. This way every Address may have 0 or 1 Customer related to it.

  2. Create a Derived Hierarchy for 1:M Relationship

Hope this helps.

SilverTry
  • 126
  • 4