May 8, 2008 at 12:03 am
Hi,
In our current requirement, a Project can have their own set of dynamic Attributes (eg: Start Date, End Date, Resolution Text, Issue details, Components impacted). Tickets/ Customer Queries are raised in Projects. We have planned to use the Multi - Tenant Fixed Column design approach where a Table will hold the Ticket Attributes for all Projects. The table design for the Attribute Values selected in a Ticket is as follows:
The values will be stored like the following:
S.No Ticket_ID Attr1_ID Attr1_Val Attr2_ID Attr2_Val Attr3_ID Attr3_Val
1 1 10 10/12/2008 21 Page Issue 22 .ASPX page modified
2 21 24 100 34 2 39 Bug information not available
We have planned to go with is approach of storing all the Attributes as Column wise since the number of Tickets is expected to 5000 and more per day (across all Projects). With this approach each Ticket will occupy one row where as if we store the Attributes Row Wise, the Table Size will grow very rapidly and the Performance would be slow.
Attr_Val dataype is planned to be set as nVarchar(..), since it can hold any Data Type Value. We are planning to restrict the maximum number of Attributes as 25.
We are facing complexity with this design approach
i. The CRU operations for Attributes in a Ticket.
ii. For search operation we are planning to use Regular Expression where as we keep the Attribute values all appended as single string in a separate Table and perform search on that.
Do you suggest whether this is a good approach? Also we have other thought of storing the Attributes Row Wise and using Partitioning in SQL Server 2005. Will that be helpful?
Thanks in advance.
Regards,
Nags
May 8, 2008 at 12:32 am
Hi
Hope i have understood you r post clearly..
I think this is an issue where design could be row wise or column wise and you can have arguments for and against both. It depends on the volume of your data and the queries that are going to hit against the tables. I leave it to the experts to debate this.
couple of issues with your design approach.
1) If you need more than 25 attributes in future , this will involve schema changes. If you have already deployed in production this can make things complex.
2) Many projects may not need 25 attributes. So you will have columns not being used .
"Keep Trying"
May 14, 2008 at 4:51 am
no response 😉
"Keep Trying"
May 23, 2008 at 5:12 am
Hi,
Thanks for your reply. We are still debating on choosing the Column Vs Extension tables approach.
I understand the constraint on fixing the attributes count to 25. We are in the mode of doing a Performance Test on both the approaches. May be after that, I will post in this group to get some light on that.
Thanks,
Nags
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply