Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; I have two option ; 1. I can create a Response table for each of ResponseType. 2. I can create a Global Response table with a ResponseType Column and create a partition for each ResponseType; Note that; Response Entity is same for each of ResponseType. Each ResponseType is equivalent.

What is your idea?

share|improve this question
    
I'm confused as to why you think you need to partition on the ResponseType column in Response. Partitioning a table is not the same as setting FAST=TRUE. –  Adam Musch Mar 6 '12 at 7:18
    
The Table Response getting querys on Column ResponseType, and The table Response has really huge data. –  dursun Mar 6 '12 at 7:21
    
Define "really huge data." If the data is really really big and you're relying on partitioning alone in the absence of indexing, you're still dead. –  Adam Musch Mar 6 '12 at 7:26
    
I have a few responseTypes let say ten or eleven or twelve, for every responseType the Response table may get a few hundered thoushands of insert per day. Current system is relying on an index which is on customerId, however id does not seem to be sufficient. –  dursun Mar 6 '12 at 13:06
1  
If you're querying for a single response code for a customer, then perhaps it makes sense to partition along response code. Without knowing more about the queries you're running, it's very difficult to answer a question of this type conclusively. –  Adam Musch Mar 6 '12 at 14:21
add comment

2 Answers

This depends on two things: the number of (expected) rows that you'll be dealing with, and the number of different fields between each ResponseType.

How many rows are you going to be dealing with? And about how many of each ResponseType? If you're talking about a solution that involves millions of rows, then partitioning the tables makes sense. However, if you're only talking about hundreds or even thousands of rows then I wouldn't worry about it.

If the fields between each ResponseType aren't very different, the you probably won't need as many individual ResponseType tables. However, if the data for each ResponseType is significantly different, then I can see the reasons behind creating (many?) multiple ResponseType tables, with one global Response table. If it were me, I would try to create as few ResponseType tables as possible.

share|improve this answer
    
Even millions of rows is nothing these days, when appropriately indexed... –  FreshPhilOfSO Mar 29 '12 at 15:57
add comment

Partitioning by response_type won't speed up your queries much. Even when you put the partkey into the where clause. The db will always have to do a fullscan of the partition. Try thinking about composite partitions or another partitioning key (e.g. response_id)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.