►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

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

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

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

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

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

BenHayat

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

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

pureist wrote
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

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.

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

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).

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

was there any indication of how straight forward it would be to introduce such functionality?
it would definitely be a worthwhile user sponsored enhancement.
..along with nested If's
..along with being able to have process buttons on forms of new instances

BenHayat wrote
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.

BenHayat

pureist wrote
was there any indication of how straight forward it would be to introduce such functionality?
it would definitely be a worthwhile user sponsored enhancement.
..along with nested If's
..along with being able to have process buttons on forms of new instances

BenHayat wrote
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.

The answer was that, there is no way to do it in Aware. When I think about it, there are so many places in IDE that the tool references object's and it's attributes and forms at design time and introducing runtime resolution of object, can create a mess in the tool.

After discussing my whole case with Support, based on "my" situation, my original approach is correct, but the implementation will not be not clean and will get grow in size. I have to think outside of the box with a clever idea how to skin this cat. 😃

BenHayat

pureist wrote
..along with nested If's

This reminds me when I was finishing my Masters, I had to take two courses on writing Compilers. For these two course we had to write a miniature Pascal compiler written in Pascal or Algol (our choice) on Univac 1108. One of the most challenging part was to be able implement parsing nested ifs and produce the final code to be assembled.

Many don't realize, Aware had to write a full language parser to understand our code. Most of the configurator tools in the market don't have a language that you can code in, where we have in our rule language. I greatly appreciated Aware because I know how complex this part is.

RocketRod

Further explanation.

I would pursue my original concept. It doesn't matter how few attributes you have in the master table but it's important that you at least put the industry there. Also to retain a master table simplifies your coding a lot. To the user they don't see any issues say in creating an item as you would use a process.

Create master with master.industry = users industry
Edit master

You then have just one Edit form for the master with each industry having its own form section based on applicability of the mater category. The industry form section displays the information as a form as these reference attributes would not have multiple ticked on for the reference. Yes the count of the master table would be large but it would be small in size.

This enables you to write one query, one add, one edit rather than a set for each industry.

I'm not sure why you require a m-2-m middle table? Maybe you could elaborate as I would just see subscriptions as another reference for the mater table?

Cheers Rod

BenHayat

RocketRod wrote
Further explanation.

I would pursue my original concept. It doesn't matter how few attributes you have in the master table but it's important that you at least put the industry there. Also to retain a master table simplifies your coding a lot. To the user they don't see any issues say in creating an item as you would use a process.

Create master with master.industry = users industry
Edit master

You then have just one Edit form for the master with each industry having its own form section based on applicability of the mater category. The industry form section displays the information as a form as these reference attributes would not have multiple ticked on for the reference. Yes the count of the master table would be large but it would be small in size.

This enables you to write one query, one add, one edit rather than a set for each industry.

I'm not sure why you require a m-2-m middle table? Maybe you could elaborate as I would just see subscriptions as another reference for the mater table?

Cheers Rod

Thanks Rod;
I need to think through the cases since each item for each industry becomes a child of a single master record for each vendor, rather than an standalone record in the it's own industry table under the vendor. Every time I need to deal with a record I first have to bring it's single parent master record into context and then fetch all it children.

For example, to create MasterBO
LoggedInUser.Industry = 'Aviation'
LoogedInUser.MasterBO.Industry=LoggedInUser.Industry

Then anytime I need to create a new AviationBO, I first have to bring it's parent into context by finding it and then assign the parent to child.
AviationBO.Parent=MasterBO

But from this point, anytime I need to find any AviationBO, I have to have to find the master for that user and then find an individual child.

Thinking out loud...

Then this raises the question, if I only have ONE masterBO per user (since a user only has ONE industry), why do I even need a MasterBO? I can make the user as the masterBO and all AviationBO hangs from a user and not a middleman, like MasterBO.

I hate middleman, they always get in your way. 😃