►Q◄ Best ways to break a large DB table???

[BenHayat]

Suppose you have an object [DB table] that you know it's going to get very large (not because it has many properties/fields, but mainly because of counts). What are the best ways at design time to break this table down?
I've come up with some ideas, but I like to hears yours as well.
Thanks!

[BLOMASKY]

don't understand what you mean, "break it down". Are you concerned with performance with a large table? and HOW large, (how many zeros?) cause 6 or 7 zeros should not be an issue.

bruce

[BenHayat]

don't understand what you mean, "break it down". Are you concerned with performance with a large table? and HOW large, (how many zeros?) cause 6 or 7 zeros should not be an issue.

bruce

Breaking it down, meaning I keep all the "restaurant" category in one table, Automobile items in another table, rather than putting them all in one table and then logically break it down by having a category field in there.

and HOW large, (how many zeros?)

It's the middle table in m-2-m that concerns me that can get very large and could become slow to find an item.

[CalD]

If Your indexs are good and your code is clean then this should be a non issue, remember you can add multi field indexes out of aware that will help.

In systems not written in aware we have massive tables (insert almost 1 mil records per day) clean code and good infrastructure its all good...

[BenHayat]

For those with DB experience. Hear me out...

Since I graduated from school back in 82, I would say, 80% of application I had written or worked on were DB and business related apps. Designing & modeling DB (either directly DB tables or OO modeling) has become very natural to me. And the history of my work has proved it (speed, reliability and accuracy) that my designs were sound and lasted for years in the field and worked properly.

Now, I'm about to do something that is against my DB modeling rules and scarifies dynamic search and storing all the records in on table that are the same nature.

For the sake of this conversation, let's say I have an "Item" object. This Item object, instead of having a conventional category reference assigned to it to logically gives it a break down (like Restaurant, Automobile, Medical supply and etc.), I actually create different objects like "Restaurant_Item, Automobile_Item, MedicalSupply_Item and etc.

PROS:
a) I can break down the volume into as many categories as I need. This is the MAIN reason going through this pain, that every time I have a new category of data, they have their own table. Volume control is the main reason.

b) This allows me to tailor each Item object and form according to that category.

c) Users using one category, will not affect other users using other categories. For example, if a bunch of users using the Automobile table will not impact those users using Medical Supplies.

CONS:
a) From programing point of view, it creates a lot repetition work on the development team.

Hearing this, what do you think? Any other ideas and thoughts? Again, the main objective is to spread the volume. It's like if you got 20 tons of dirt to carry, would you use a 10-wheeler or an 18-wheeler.

[RocketRod]

Ben in your example I would tend to use more of a hybrid structure. I would have a master item BO which contained the common elements such as item description, item cost, item name etc with subsidiary BO's for each category where you have elements peculiar to just that item category. This can be implemented easily with AwareIM and gives you best of both worlds in that all items are listed in one table but users see specific forms related to each subsidiary BO. It would I believe cut down your duplicate work whilst maintaining clarity of design and therefore ease of maintenance.

Cheers Rod

[BenHayat]

Ben in your example I would tend to use more of a hybrid structure. I would have a master item BO which contained the common elements such as item description, item cost, item name etc with subsidiary BO's for each category where you have elements peculiar to just that item category. This can be implemented easily with AwareIM and gives you best of both worlds in that all items are listed in one table but users see specific forms related to each subsidiary BO. It would I believe cut down your duplicate work whilst maintaining clarity of design and therefore ease of maintenance.

Cheers Rod

Thanks Rod;
Allow me to expand further to disclose more reality of the case. Spreading the "item" object among multiple types of objects isn't my "real" concern, but I used it to illustrate a point. Here is the closer view of the case.

Imagine we have the Item file and we have another table called User_Subscription which has a m-2-m relationship. For this I actually create a middleman object that holds reference of both tables (Item and subscription) and some other attributes.
The max size of this middleman object can be calculated as Items_Count * Subscriptions_Count, so if I have 100,000 items and 1000 subscriptions, the middleman object can potentially become, 100,000,000. And this is where the real problem starts, as both sides of the equation (Items and subscription) get larger, the middleman object gets too large too fast.

Considering your suggestion, I can have the Item core as the master, then I can have Item_Subsidiary as an extension or inheritance of Master, however I can then have multiple "Middleman" objects based on the subsidiary category and not the item. Middleman references Item_Subsidiary and Subscription.

This hybrid direction still solves spreading the load of middleman object and yet not as much code replication. What do you think?
Thank you...

[johntalbott]

Ben in your example I would tend to use more of a hybrid structure. I would have a master item BO which contained the common elements such as item description, item cost, item name etc with subsidiary BO's for each category where you have elements peculiar to just that item category. This can be implemented easily with AwareIM and gives you best of both worlds in that all items are listed in one table but users see specific forms related to each subsidiary BO. It would I believe cut down your duplicate work whilst maintaining clarity of design and therefore ease of maintenance.

Cheers Rod

+1

[BenHayat]

Ben in your example I would tend to use more of a hybrid structure. I would have a master item BO which contained the common elements such as item description, item cost, item name etc with subsidiary BO's for each category where you have elements peculiar to just that item category. This can be implemented easily with AwareIM and gives you best of both worlds in that all items are listed in one table but users see specific forms related to each subsidiary BO. It would I believe cut down your duplicate work whilst maintaining clarity of design and therefore ease of maintenance.

Cheers Rod

Rod, as I gave more thought to your suggestion, I run into an issue.

For example if I had a T-Shirt Item with certain description and price, but different colors and sizes, your solution is very applicable.
I create a T-Shirt Item with Name, description and Price.
I then create a subsidiary extension that holds reference to item and carries color and size, so I will have 1 item and 6 subsidiary records (White/small, White/Mid, White/Large, Black/Small, Black/Mid, Black/Large). It works fine.

But my item's shape changes from category to category. Even the name is different, for example an Automobile item and MedicalSupply item have nothing in common.
So, I can't just create a single item with no commonality in it, where the subsidiary contains all the attributes and that's why an Automobile_Item BO and MedicalSupply_Item BO have a different description and name.

The only thing that they share, is the fact that they are items for sale from user's perspective.
So I have many items, but depending the category and user base, their shape is different. So do you think the Hybrid still applies in this case?

[pureist]

are you saying that an 'item' in your scenario does not have any or only has a couple of attributes which are applicable to all options/permutations of that item?

and therefore the 'item' you ultimately sell is really just a construct/concatenation of different random 'options'?

..like an assembly/bill of materials constituting the finished item in a way in that one finished item can be completely different to the next.

..and that there is no systematic 'structure' or order for constructing the items. for example, the second option comprising the item isn't always 'size', it might be 'shape' because size isn't applicable to the ultimate item being constructed and sold.

If so, does the user compile/build the item themselves by selecting various options, or are all possible permutations/combinations of 'options' which can comprise items which can ultimately be sold setup in a master file in advance?

[BenHayat]

are you saying...

You're drifting into another direction that has nothing to do with my question.
Simply asking, I have "Items" that relate to different industries (the only common attributes I see is name and description). An item in Automobile has totally different attributes than an item in Shoe manufacturing. From user's POV they're dealing with an Item in their own industry.
There might be 50 industries I want to cover, my question is, instead of creating one huge object with all the attributes and storing ALL items in ONE DB table, isn't better to create 50 custom objects, which means 50 different tables?

[pureist]

so it is like i was suggesting.. an 'item' in your scenario does not have any or only has a couple of attributes which are common throughout..
..and the ultimate item sold is made up of this 'foundation' common item plus various non-systematically structured 'options'/attributes appended thereafter.

Then I would say that each 'option' should be a separate object.

but how you would implement it still depends on whether the user will append the options to the base item, or whether the system admin builds a master file of all valid possible combinations of base item and options.

[BenHayat]

but how you would implement it still depends on whether the user will append the options to the base item, or whether the system admin builds a master file of all valid possible combinations of base item and options.

Having a base item or master item doesn't really bring me any help, by saving two attributes, but it creates allot more complexities for end user to create a master item first and then attach all those option records to it. It makes sense with my T-Shirt example.
I'm back to square one that each item for each industry has to be it's own industry item all inclusive.
This isn't so bad, but I need to figure out another problem, by resolving Object Name at runtime and not design time.
So, if the customer has chosen Aviation as his industry in his profile, when he is creating the item,

I want to write
Create Customer.Industry with Customer.Industry.Description='ABC'
in here, the Customer.Industry should resolve to Aviation and Aware to create me an Aviation Object with description='ABC'.

if I can't do that, then I have to have allot of ugly IF statements.

[pureist]

you could use START PROCESS prefix+Customer.Industry+suffix , but it's still not elegant as you would have to have all the different processes setup in the configuration
It's a shame there isn't a convention in rules like with << >> in HTML such that text enclosed in ( ) or { } or even << >> or < > is taken to mean at runtime that the text used is taken from an attribute value.
..in a way like @@(<cell coordinates>) used to do in Lotus 1-2-3, where the @@ function would return the value in the cell according to <cell coordinates>.
Then an entire Action could be stored in an attribute or attributes (and concatenated together even).

[BenHayat]

you could use START PROCESS prefix+Customer.Industry+suffix , but it's still not elegant as you would have to have all the different processes setup in the configuration
It's a shame there isn't a convention in rules like with << >> in HTML such that text enclosed in ( ) or { } or even << >> or < > is taken to mean at runtime that the text used is taken from an attribute value.
..in a way like @@(<cell coordinates>) used to do in Lotus 1-2-3, where the @@ function would return the value in the cell according to <cell coordinates>.
Then an entire Action could be stored in an attribute or attributes (and concatenated together even).

After verifying with support, we can't resolve Object name at runtime to get from an attribute. This really kills a great design and idea I had to keep the code clean.