Return to Question

1. Have separate column for each of the hobbies. Pro: Single contact record with hobbies Cons: What happens when a new hobby needs to be created? Import automation will need to changed to cater for new hobby. Could end up having hundreds of columns (scalability?).

2. Have single column to store hobbies. Pro: No need to make changes to the automation when new hobby is imported. Can be easily referenced in the journeys by looking at just one field. Cons: Could end up having a large number of records as contacts are repeated for each hobby (scalability?).

3. Store each file in a different data extension Cons: Automation will need to be modified and create a new data extension each time a new hobby is created.

1. Have separate column for each of the hobbies.

Pros: Single contact record with hobbies.

Cons: What happens when a new hobby needs to be created?

Cons: Import automation will need to changed to cater for new hobby.

Cons: Could end up having hundreds of columns (scalability?).

2. Have single column to store hobbies.

Pros: No need to make changes to the automation when new hobby is imported.

Pros: Can be easily referenced in the journeys by looking at just one field.

Cons: Could end up having a large number of records as contacts are repeated for each hobby (scalability?).

3. Store each file in a different data extension

Cons: Automation will need to be modified and create a new data extension each time a new hobby is created.

File 1: | ContactID | Hobby | -------- | -------------- | ContId111 | Hobby1
| ContId222 | Hobby1
| ContId333 | Hobby1

File 2: | ContactID | Hobby | -------- | -------------- | ContId111 | Hobby2
| ContId222 | Hobby2
| ContId444 | Hobby2

File 3: | ContactID | Hobby | -------- | -------------- | ContId111 | Hobby3
| ContId555 | Hobby3

File 1:

File 2:

File 3:

I'm trying to come with a data extension schema to store an attribute that could contain a number of different values (eg. hobby). We will get a daily extracts of files with a list of contacts and their related hobbies. We want create an automation that imports these files into a data extension. So for example:

File 1: | ContactID | Hobby | | -------- | -------------- | | ContId111 | Hobby1 | | ContId222 | Hobby1 | | ContId333 | Hobby1 |

File 2: | ContactID | Hobby | | -------- | -------------- | | ContId111 | Hobby2 | | ContId222 | Hobby2 | | ContId444 | Hobby2 |

File 3: | ContactID | Hobby | | -------- | -------------- | | ContId111 | Hobby3 | | ContId555 | Hobby3 |

Please note if there is new hobby then a separate file will be created and we will need to import that in the same data extension. Options considered:

1. Have separate column for each of the hobbies. Pro: Single contact record with hobbies Cons: What happens when a new hobby needs to be created? Import automation will need to changed to cater for new hobby. Could end up having hundreds of columns (scalability?).

2. Have single column to store hobbies. Pro: No need to make changes to the automation when new hobby is imported. Can be easily referenced in the journeys by looking at just one field. Cons: Could end up having a large number of records as contacts are repeated for each hobby (scalability?).

3. Store each file in a different data extension Cons: Automation will need to be modified and create a new data extension each time a new hobby is created.

My question - What is the best way to create an automation to import these files into a data extension and how do we structure the data extension? Please note I'm using hobby as an example but in real life there could be hundreds of hobbies and millions of contacts.

I'm trying to come with a data extension schema to store an attribute that could contain a number of different values (eg. hobby). We will get a daily extracts of files with a list of contacts and their related hobbies. We want create an automation that imports these files into a data extension. So for example:

File 1: | ContactID | Hobby | -------- | -------------- | ContId111 | Hobby1 
| ContId222 | Hobby1 
| ContId333 | Hobby1

File 2: | ContactID | Hobby | -------- | -------------- | ContId111 | Hobby2 
| ContId222 | Hobby2 
| ContId444 | Hobby2

File 3: | ContactID | Hobby | -------- | -------------- | ContId111 | Hobby3 
| ContId555 | Hobby3

Please note if there is new hobby then a separate file will be created and we will need to import that in the same data extension. Options considered:

1. Have separate column for each of the hobbies. Pro: Single contact record with hobbies Cons: What happens when a new hobby needs to be created? Import automation will need to changed to cater for new hobby. Could end up having hundreds of columns (scalability?).

2. Have single column to store hobbies. Pro: No need to make changes to the automation when new hobby is imported. Can be easily referenced in the journeys by looking at just one field. Cons: Could end up having a large number of records as contacts are repeated for each hobby (scalability?).

3. Store each file in a different data extension Cons: Automation will need to be modified and create a new data extension each time a new hobby is created.

My question - What is the best way to create an automation to import these files into a data extension and how do we structure the data extension? Please note I'm using hobby as an example but in real life there could be hundreds of hobbies and millions of contacts.