CODEWITHSUNDEEP
databasedatabase-designdata-structures
Kulin Choksi
Kulin Choksi

Reputation: 761

Database structure for dynamic requirements

In our Ticket System, as per varying requirements of tickets, we need varying table structure to store different type of fields for each type of ticket. e.g. Ticket for customer complain for a product, requires customer no. and product id but Ticket for simple inquiry doesn't require product id. Some type of ticket may not require Customer no. also.

We also need Analytic and Reporting features for this system which includes such dynamic details like product detail and customer details.

As a solution, I've thought of Non-SQL DB (Cassandra here), which could support dynamic DB structure and improves efficiency for analytic and reporting functionality. But I've not used any Non-SQL DB before, so I'm not very sure for this solution.

Can anybody suggest any better solution or give some light on thought of Non-SQL solution for this problem?

Upvotes: 0

Views: 191

Answers (2)

Woot4Moo
Woot4Moo

Reputation: 24316

The design I would recommend would be similar to how I would do it in an object oriented language. This will incur the overhead of potentially complex joins and the overhead of multiple tables. I would lay my database out like so: 

Table GeneralTickets  
(  
    ticket_id number,  
    customer_id number,
    ... other fields that are **common to all tickets**
)    

Table ComplaintTickets
(  
    complaint_id number,
    ticket_id number (FK to GeneralTickets#ticket_id),  
    product_id number,
    ... other fields
)   

Table FeatureTickets  
(  
    feature_id number,
    ticket_id number (FK to GeneralTickets#ticket_id),  
    ... other fields
)

A No-SQL solution is not what you are looking for in this case, as this problem is 1) more appropriate for a relation database and 2) I doubt highly you recording as much data as would be appropriate for a No-SQL solution.

UPDATE

In terms of the reporting feature, I am still sticking with the relational database on this one. You essentially have a data warehouse problem that needs to be solved. What you want to do is have a series of base tables (these will be the tables that ultimately build your reports). From these base tables you will create what are called materialized views. These materialized views will handle all the data after it has been calculated for a given time frame (i.e. your report for January).

As you said, overhead of complex joins and multiple tables is the main factor I'm thinking of No-SQL solution. SQL solution would increase maintenance and low down the performance also. I think, ticket system is not tightly bound to other systems and for other details like product details or customer details, we may sync from SQL DB on change events. Can you please explain how do you think of 2nd problem?

Now to the comment you left. The SQL will only be slow if it is written poorly, this is the difference between a cartesian join and a simple index lookup. The maintenance does play a small part in all of this. However, I cannot speak to the maintenance aspect without seeing your domain objects (the classes that are initialized after you process a result set). It is possible that your domain needs to be modified to be more correct than it is now (not saying it is incorrect, just that it could be more correct). Keeping multiple systems in sync is going to be one of the last things you want to do.

Upvotes: 1

Leon Bouquiet
Leon Bouquiet

Reputation: 4372

Call me old-fashioned, but I think I would suggest a regular relational database. Partly because I would be surprised if the "varying requirements" are actually that varying that they warrant a NoSQL solution rather than referential integrity and ACID properties. And partly because I know that for regular RDBMS's there are a wide variety of reporting solutions available.

I mean, can't you just model a ticket with optional (i.e. nullable) fields, or define several subtypes of tickets (i.e. using inheritance - see http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx).

Upvotes: 0

Related Questions