Help to define metadata

[alalan]

Hi Folks!

I need your brain to know how do I create a metadata for my database which will be captured from the CRM and I will use for reporting later.

As you can see in the attached picture, I have project name and what services we provided them. Later, while reporting I would like to know how many projects took "abc" services from us. I don't want to duplicate rows for same project name and different services as well as how do I capture this data from the CRM.

[ebeery · Accepted solution]

@alalan can you explain what you mean by "I don't want to duplicate rows for same project name and different services"?

It seems to me that in order to make any meaningful use of this data it would need to be in the form:

Project Name - external	Services	Sector
Test1	Interior Design	Hospitality
Test1	Construction	Hospitality
Test1	Documentation	Hospitality
Test1	Construction Services	Hospitality
Test1	Town Planning	Hospitality
Test2	Feasibility	Multi Residential
Test2	Town Planning	Multi Residential
Test2	Interior Design	Multi Residential
Test2	Construction	Multi Residential
Test2	Documentation	Multi Residential
Test2	Construction Services	Multi Residential

If you wanted to further normalize and reduce the redundancy of the "Sector" info, you could have two tables like below, related through a one-to-many relationship.

Project Name - external	Services
Test1	Interior Design
Test1	Construction
Test1	Documentation
Test1	Construction Services
Test1	Town Planning
Test2	Feasibility
Test2	Town Planning
Test2	Interior Design
Test2	Construction
Test2	Documentation
Test2	Construction Services

Project Name - external	Sector
Test1	Hospitality
Test2	Multi Residential

[Ashish_Mathur · Accepted solution]

Hi,

With the current data layout, there is nothing that you can do at all.  To get any meaningful analysis, you must ensure that each service appears in its own cell (which also means that in the Project Name and Sector columns, the entries will repeat.

[ebeery · Accepted solution]

@alalan can you explain what you mean by "I don't want to duplicate rows for same project name and different services"?

It seems to me that in order to make any meaningful use of this data it would need to be in the form:

Project Name - external	Services	Sector
Test1	Interior Design	Hospitality
Test1	Construction	Hospitality
Test1	Documentation	Hospitality
Test1	Construction Services	Hospitality
Test1	Town Planning	Hospitality
Test2	Feasibility	Multi Residential
Test2	Town Planning	Multi Residential
Test2	Interior Design	Multi Residential
Test2	Construction	Multi Residential
Test2	Documentation	Multi Residential
Test2	Construction Services	Multi Residential

If you wanted to further normalize and reduce the redundancy of the "Sector" info, you could have two tables like below, related through a one-to-many relationship.

Project Name - external	Services
Test1	Interior Design
Test1	Construction
Test1	Documentation
Test1	Construction Services
Test1	Town Planning
Test2	Feasibility
Test2	Town Planning
Test2	Interior Design
Test2	Construction
Test2	Documentation
Test2	Construction Services

Project Name - external	Sector
Test1	Hospitality
Test2	Multi Residential