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!
►Q◄ Best ways to break a large DB table???
Re: ►Q◄ Best ways to break a large DB table???
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
bruce
Re: ►Q◄ Best ways to break a large DB table???
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.BLOMASKY wrote: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
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.and HOW large, (how many zeros?)
Re: ►Q◄ Best ways to break a large DB table???
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...
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...
Re: ►Q◄ Best ways to break a large DB table???
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.
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.
Re: ►Q◄ Best ways to break a large DB table???
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
Cheers Rod
Re: ►Q◄ Best ways to break a large DB table???
Thanks Rod;RocketRod wrote: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
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...
Last edited by BenHayat on Mon Jul 25, 2016 1:05 am, edited 1 time in total.
-
- Posts: 619
- Joined: Wed Jun 17, 2015 11:16 pm
- Location: Omaha, Nebraska
- Contact:
Re: ►Q◄ Best ways to break a large DB table???
+1RocketRod wrote: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
VocalDay Solutions - Agility - Predictability - Quality
We specialize in enabling business through the innovative use of technology.
AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
We specialize in enabling business through the innovative use of technology.
AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
Re: ►Q◄ Best ways to break a large DB table???
Rod, as I gave more thought to your suggestion, I run into an issue.RocketRod wrote: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
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?
Re: ►Q◄ Best ways to break a large DB table???
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?
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?
Last edited by pureist on Mon Jul 25, 2016 7:01 am, edited 1 time in total.
Re: ►Q◄ Best ways to break a large DB table???
You're drifting into another direction that has nothing to do with my question.pureist wrote:are you saying...
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?
Re: ►Q◄ Best ways to break a large DB table???
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.
..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.
Re: ►Q◄ Best ways to break a large DB table???
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.pureist wrote: 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.
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.
Re: ►Q◄ Best ways to break a large DB table???
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).
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).
Re: ►Q◄ Best ways to break a large DB table???
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.pureist wrote: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).