Juntion Table(s)

[khurram7x]

I've a design issue, yet again. I've two tables with many-to-many relationship, where number of activities could be done on an equipment, and similarly equipment might also be treated under certain activities. Junction table between those tables comes up with different set of requirements for each type of activity, on each equipment.

In attachment 1, I've created my situation where I have Activity and Equipment table and the Junction table between them. Now there are several different kind of activities and each activity appears to have a different set of attributes for the Junction table. This is what I've tried to described in attachment 2.

If I try to create a different Junction Table for each different activity/equipment combination, then there will be several Junction Tables connecting to the same Activity and Equipment table. Will it be a correct approach according to database design rules??

I am wondering what are the other possibilities to deal with such situation, if above approach is not correct?? I've tried several approaches but in every approach there is something which makes me turn it down.

Each activity is later saved in History Table on daily basis to get the progress graph, as hinted in attachment 1. This will later be verified weekly from history table and any excess or missed quantity will be adjusted accordingly. I'm mentioning this because when it'll be verified, I'll then need to go back and make modifications in relevant tables via some automatic procedure according to adjusted amount. I'm mentioning this because maybe it'll have an impact in a way i am trying to design a database.

I'm really in need of bit of a quick help here with very limited time left in project completion. Any help will be appreciated.

Thank you,
K

 

[James Deckert]

Having several junction tables doesn't seem right to me.
You could have all your fields in a single junction table, but you should work hard at generifying (made up word here) as much as possible. e.g. Don't have a 'dressed date' for one activity and a 'completion date' for another, simply have a 'completion date'
You also need to make sure the fields belong in the junction table and not in the activity table or somewhere else.

 

[jdraw]

I suggest you give us some specifics about your business. What happens in a typical day? What are the things involved? Perhaps you could expand on Activity and Equipment so readers get a real "feel" of what you are trying to automate. Readers need to understand the business before they can provide focused responses on design.
Good luck.

 

[spikepl]

If each activity has an entirely disparate set of parameters you might consider this: https://en.wikipedia.org/wiki/Entity–attribute–value_model

 

[khurram7x]

Well this is something difficult for me to explain without writing a very long article, but in short, we're an electrical services company in an oil industry. This part is the core part performing all the calculations. I'll not try to explain formula's behind calculations because they're pretty complex but in this part each activity is sub-categorized from bigger activities. in above attachments I've shown Cable Installation, Cable Termination, Cables Dressing, etc which are few of the types of these activities. Each type of activity is performed on several equipments and amount of work done in terms of Quantity and Hours adds up to the bigger picture.

I'm trying to add amount of work done from each activity, on each equipment, and then add it in activity table. There might be other designs, probably easier than I mention and which i could not think... and I'm still not getting over it.

 

[khurram7x]

I've another design idea coming in and that is, instead of making a Junction Table Activity_Equipment, I create Equipment table a sub-table of Activity Tables since every Activity is going to be done on some equipment. And then make each ActivityType a new table and each ActivityType table will be a sub table of Equipment table (In my first original question, ActivityType is a Junction Table).
But then, is there going to be a Junction table between Activity and Equipment table... because 1 activity could be perform on several equipments, and similarly each equipment might have several activities? Or there might be no Junction table, since there's no common attribute??
And then each ActivityType table could be used for several equipments, but since each ActivityType is a separate table, there's no need for Junction tables?

 

[spikepl]

Your 2.jpg describes the physical goings on of each activity.

Your 1.jpg lists stuff i'd expect to be part of a junction table, i.e there is a budget for an activity and a piece of equipment, and then there are the measured values for that activity and equipment: both go into the junction table since both are related to both activity and that single piece of equipment, right?

 

[spikepl]

Do what jdraw suggested - explain more or give one specific example encompassing both activity and equipment. We are still guessing what is what.

 

[khurram7x]

Your 2.jpg describes the physical goings on of each activity.

Your 1.jpg lists stuff i'd expect to be part of a junction table, i.e there is a budget for an activity and a piece of equipment, and then there are the measured values for that activity and equipment: both go into the junction table since both are related to both activity and that single piece of equipment, right?

Thanks spike, you're right that stuff in 1.jpg is more like stuff to be in junction table but the way things are calculated in budget or base values are too complicated and spread over several tables!!

It took my huge time to understand the process below, so I don't know how to explain it well especially when I'm not good in explaining things.

The attachments in first message and my last message gives an idea about the business of our company. Attachment with this message is a relationship diagram of this database, this time I'm pasting a larger resolution to get as much as I could.
Right from the beginning, there are many different types of installation, shown in attachment RulesOfCredit_InstallationType. Percentage of each activity within a full job pack is mentioned here, and total percent is not more than 100 %. Each of this installation type has its own set of activities, called Progress Gates, shown in attachment RulesOfCredit_ProgressGates. The percentage from InstallationType has been carried forward and divided in its own 100% within each set of activities/progressgates.

Then there's a table BoQ (Bill of Qantities) which contains the budgeted quantities, amounts, manhours, etc etc... attached. And ProgressGates calculations are applied on these value to produce tblBoQ_Progress.

Each progressgate in BoQ_Progress is a complete and separate activity, for example ID 515 in tblBoQ_Progress table is 'Pull Cable' and that is where Cable Pulling table comes in. 516 is 'Dressing' and 522, 524 are 'Glanding' and 'Termination' activity. Activity is performed on equipments from Equipment table. Since the values in this table are calculations, i'm only editing records here. At each edit, a new record is created in 'tblProgress_table' which is used for history purposes and checking progress, charts, etc. and this is where i requested advice. Pretty COMPLEX!!... and then cable pulling, termination, and other tables (the one's that i represented in 2.jpg in the very begining) have their own calculations. It should be in reality, like, the sum from all the sub-pulling activities on various cable types (equipment) 'Pull Cable' ProgressGate should be added and reflect in Progress table, but it is making things even more complicated and I've not done so. But for some ProgressGates in future, it might become a requirement.

Thanks

 

[spikepl]

Before I attempt to digest all this (and it won't be now) how is all this dealt with today? Your company exists, and functions. presumably, so there must be one or more workflows.

If you want proper help, present the workflows, rather than letting us reverse-engineer them from what you attempt to do with a db, which is difficult. Also, already there you are likely to introduce restrictions based on your own perceptions. If the issue is complex and demands a lot of effort, then I'd suggest you hire someone.

 

[khurram7x]

Before I attempt to digest all this (and it won't be now) how is all this dealt with today? Your company exists, and functions. presumably, so there must be one or more workflows.

If you want proper help, present the workflows, rather than letting us reverse-engineer them from what you attempt to do with a db, which is difficult. Also, already there you are likely to introduce restrictions based on your own perceptions. If the issue is complex and demands a lot of effort, then I'd suggest you hire someone.

Spike, I've requested for workflows. I'll post them by tomorrow if I get them. Thank you.

That's the catch, company wanted to hire the database guy but I stepped forward and requested a chance. I am an enthusiast, so I think with a little help I'll be able to overcome. Believe me here in this forum, I've got many answers which i couldn't get anywhere. Now I am in a much better position to program than few months back. Thanks to all of you.

 

[jdraw]

You may get some insight from this post.

Good luck.

 

[spikepl]

Be very careful jobwise. If you plan on becoming a full-time DBA or DB developer then fine.

If you do this part time only, your company will be happy to get programming on the cheap (seemingly!) but you will in the future always get out-competed by experts on either side (programming or your business specialty). You could argue that companies need people able to sit on both sides of the fence, and they do, but then never ever search for that explicitly, so it is a truly lousy selling point.

Career-wise there is much more value in learning how to contract programming out i.e. requirements specifications, conditions, timing, development model (see waterflow vs. agile), and how to supervise software contracts. This is of course the third line in possible career advancement in any company (line management, specialist or project management)

 

[khurram7x]

Be very careful jobwise. If you plan on becoming a full-time DBA or DB developer then fine.

Career-wise there is much more value in learning how to contract programming out i.e. requirements specifications, conditions, timing, development model (see waterflow vs. agile), and how to supervise software contracts. This is of course the third line in possible career advancement in any company (line management, specialist or project management)

Thank you Spike, I've taken your advice and definitely act on these workflows as soon as I get some relaxation from current works. I am more interested in DB side of a career at this stage.

Regarding workflows, the guy who requested me to develop says that it's a new system which will overcome the old one so we don't have workflows at this stage. He sits with me and told him the issue, and suggested the other way around. This has worked for me for now at least.

Thank you.