Modelling data for Analysis & reports

Question

I am trying to build a star schema for some engraving data. The egraving data i get along with my orders from my source is in the below format.

Ex 1. BK: S2: This ^ May, 2018 ^ is engraved ^ on my ring
Ex 2. LFT: S2: Love You  ^ SY#LValways SY#EWA

In the above 2 examples.

• BK(Back),LFT(left front) represents the location of engraving.
• S2: represents the font.
• ^ represents a new line
• SY#LV SY#EWA represents the symbol to be engraved

I would build a datamodel so i am able to answer the following business questions

1. Most used location.
2. count of symbols used in an order.
3. Most used font.
4. No of multiline orders.

Below is the design i am thinking to build.

1. Attribute Desc Table. This will have one attribute value for each attribute value like font ,symbol, text

   Attribute	Attribute desc
   1	font
   2	location
   3	symbol
   4	Text

2. Attribute table. This will store a new row for each attribute of the engraving order.

For Ex

| Order number | Attribute  | Attribute value | line number|
| -------- | -------------- |
|1234      | 2              |BK               | 1          |
|1234      | 1              |S2               | 1          |     
|1234      | 4              | This            | 1          |
|1234      | 2              |BK               | 2          |
|1234      | 1              |S2               | 2          |     
|1234      | 4              | May, 2018       | 2          |
|1234      | 2              |BK               | 3          |
|1234      | 1              |S2               | 3          |     
|1234      | 4              | is engraved     | 3          |
|1234      | 2              |BK               | 4          |
|1234      | 1              |S2               | 4          |     
|1234      | 4              | on my ring      | 4          |

Is this the correct data model design. Is there a better data model design for this problem.

Answer 1

To me, it looks OK. If, by any chance, a new attribute appears in the future, you'll easily add it into the attribute_desc table and use it in attribute.

Make sure to create primary key on attribute_desc.attribute column which will then be referenced by attribute.attribute column (I'm talking about a foreign key).