Self Join Table and Related Tables
In my inventory database, I have raw materials and assemblies. An expert here said to make this a self-join situation and to make everything a "part". Then make a self-join table and start building assemblies by referencing what parts and/or other assemblies they consist of.
My follow up question in this situation is this. My assemblies are dynamically different. Some need columns for "shaft length" of pipe, others need "capacity" columns to show the type of load they can carry, and so on for about 1,000 parts. So, experts here suggested I break my assemblies up into separate tables and give them specific columns they need rather than having a master table with tons of null values.
My question is, though, with a self-join situation--how does this come into play? I have welded pipe as an assembly, I have tons of simple raw materials, I have pumps that consist of weird other parameters (columns), and so on. If I put everything in the same table in order to self-join...where do I store all this other data that is specific to different types of product so that users can search these types of fields?
My follow up question in this situation is this. My assemblies are dynamically different. Some need columns for "shaft length" of pipe, others need "capacity" columns to show the type of load they can carry, and so on for about 1,000 parts. So, experts here suggested I break my assemblies up into separate tables and give them specific columns they need rather than having a master table with tons of null values.
My question is, though, with a self-join situation--how does this come into play? I have welded pipe as an assembly, I have tons of simple raw materials, I have pumps that consist of weird other parameters (columns), and so on. If I put everything in the same table in order to self-join...where do I store all this other data that is specific to different types of product so that users can search these types of fields?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I suppose, if every category shares the same attributes, I could do this:
create a categories table with just category_id...
then instead of mapping parts to attributes, I could map categories to attributes...
Then map each part to attributes as necessary the same way you suggested...but...for the purposes of programming, when someone picked a category, the db would know which attributes to offer them to insert.
Sound right?
create a categories table with just category_id...
then instead of mapping parts to attributes, I could map categories to attributes...
Then map each part to attributes as necessary the same way you suggested...but...for the purposes of programming, when someone picked a category, the db would know which attributes to offer them to insert.
Sound right?
ASKER
Here's a link to show what i mean: http://www.rowejd.com/db_example.pdf
The link returns 404. Nice background, though.
You certainly could move the attributes mapping to the category level, but that can pin you in down the line. Say I sell comforters. A particular style can be sold in stock sizes or custom sizes defined by the customer. The former will have a set of options for length and width, while the custom dimensions can be anything in between.
If all of your products/parts within the same category will ALWAYS have the same attributes, then your strategy will work fine. If you foresee the need to have products in the same category use different attributes, it would be better to start with them at that level.
You certainly could move the attributes mapping to the category level, but that can pin you in down the line. Say I sell comforters. A particular style can be sold in stock sizes or custom sizes defined by the customer. The former will have a set of options for length and width, while the custom dimensions can be anything in between.
If all of your products/parts within the same category will ALWAYS have the same attributes, then your strategy will work fine. If you foresee the need to have products in the same category use different attributes, it would be better to start with them at that level.
ASKER
Sorry...something got messed up with dhcp...just renewed it and uploaded the file. It's there now if you wanna look.
The diagram is a perfect reproduction of this concept. Now the real work begins, yes?
ASKER
Sweet. Well, you said above "that would be a longer post" talking about some specifics about how you did what you did in a real live situation.
Here's a very open-ended "give me advice" question where you can dump whatever you'd like as far as your methods, strategy, what to do, what not to do, etc...
https://www.experts-exchange.com/questions/22900080/Advice-on-complex-data-relationships-as-relates-to-development-forms.html
Let me know whatever advice would be "another post" eh?
Here's a very open-ended "give me advice" question where you can dump whatever you'd like as far as your methods, strategy, what to do, what not to do, etc...
https://www.experts-exchange.com/questions/22900080/Advice-on-complex-data-relationships-as-relates-to-development-forms.html
Let me know whatever advice would be "another post" eh?
ASKER
I'm also curious (answer this on the other one if you wish) if it took you 9 months full time, or as one project of many working a couple hours a day, or what? I've got my login system, a method of differentiating users from admins. I wrote a PHP class for handling simple "lookup" tables. I've got the interface design done. I have the user section to where they can add addresses (branches, addresses, contact info). So now I'm to the meat of it with structuring the products and assemblies and whatnot. Just curious--again you can answer that on the other question if you wish since it's more about that sort of generality anyway.
Like I said, a longer post. :) If you check my profile, you'll see the basic timeline for this. I need to update it, though.
I started with the company in Dec2005. I was hired as the on-staff programmer. In reality, I'm the programmer, the designer, the server admin, the network guru, and local help desk. I'm one of two people in the entire company that knows anything about computers, and the other person functions as an HR administrator. Everything I.T. goes through me at some point. Most of it gets done by me directly. While I did work on other various things during most of my tenure to-date, I would say the majority of my time (6-12 hours a day on average) was dedicated to development of the new engine and underlying structure.
When I was hired, my primary responsibility was to keep up with the existing site. It had been programmed by someone else 2-1/2 years before I arrived, and it was in sorry condition. It looked like it had been coded by a third-grader. The database was in slightly better condition, but was severely limited by the MySQL version in which it was authored. For the first two months, I tried to tweak and revise as I could, but I saw too many structural problems that would prevent us from moving forward or expanding the site. At the very least, the old version of MySQL (3.23, IIRC) did not allow for sub-queries. As a result, the site dragged on the more complex operations. One page used to export order data to our production system took more than 20 minutes at times, and that has to be run every day at the start of business. I decided it was time for them to start from scratch, and began writing a brand new engine in Feb2006.
The first 'public' (publicly available, but not advertised, and not taking business) beta came 5 months later, in the middle of July. The beta consisted of being able to create an account and login, manipulate the cart in a variety of ways, and complete an order with a credit card. As the beta came up, the people who actually knew the products sent me configuration data. But I didn't know anything about the product, so configuring them was a lot like playing 'telephone'. The next month was dedicated to a versatile product manager so I could push that job to someone with knowledge. This included management of product-level fields, as well as option types, options, images, pricing charts, etc. At the same time, I created a basic order manager, and sample ordering mechanism. As I added functionality to the admin panel, I began tying all the different sections together. I made them modular so they could be called from anywhere, but each was its own little program. There were no classes yet...all of this was done strictly in PHP function-based code with the database holding data only.
Finally, near the end of October, the site actually went live. We had received a few orders (first month, we got 2!), and were able to give the system its first real, live, public testing. It was around this time I discovered a fatal flaw in my structure.
You have to realize that this is all coded by me, straight out of my head. If you go back through the few questions I posted here during that time, you'll see where I struggled with basic CSS concept and HTML portability. Even with the finished site, as it stands now with v2.0, there are only two sections of code that did not come entirely from me: an email class (still heavily edited by me to do certain things) and the captcha class (on my list for expansion when I get time). You can't ever account for all the problems you are bound to encounter, but good development is an exercise in catching the really bad ones before they become important. Sometimes you can find an error, and it only takes a day or two to back up and try again. In this instance, the flaw was in a basic concept of part of my structure. There was no hope for repair - a complete rewrite was necessary.
I started the second rewrite in Jan07. At first, the main concern was the custom CMS. We wanted to be able to supply SEO headers and meta tags to pages without editing code. I abstracted all of that back to the database. Now, every page goes through an 'overlord' page...a loader which is called mod_rewrite to assist in rendering the requested content. The loader calls the database, recognizes which PHP include files to use, which meta tags to write, etc. That change alone required converting every page on the site into the new format...breaking the pages so one half handles all the PHP, and the other handles only HTML and presentation. Since we were going to use the engine for more than one site, I had to be able to configure it easily to work with different rules. The table handling these differences tells the engine if the cart module should be available, if customer accounts are required, or if certain surcharges are added to the total. All of the PHP was modified to use this information. In the old version, pricing an item was done through an algorithm inside the page. My latest change (completed last night, btw) was to abstract that process into the database. This centralized my pricing algorithm into a procedure that could be called from anywhere, including remote sites. That will be important for us later when we want to provide remote pricing to franchises from our main product database, or maybe from an AJAX call for dynamic forms.
Centralization was a primary goal of the second rewrite. Previously, the code was all function-based. I had hundreds of functions and managing them was a serious issue. The first step of the rewrite was to upgrade to MySQL 5 and PHP 5. This opened up classes in PHP, and stored procedures (like the pricing algorithm) in MySQL. All of the functions were rewritten into classes. Code that used to be 100 lines to pull an order summary can now be accomplished in 4 or 5 instead. If I decide to change the way a particular item works, I only have to change it one place - the class that handles it.
All through the rewrite, I was not just 'fixing' existing items. I was also expanding our abilities. All of the CSS work I did not have time for before was completed...almost all style is now controlled by a company-defined style sheet. The account management system was expanded to allow for saving/editing address profiles, as well as basic quote/order management. We now have the ability to accept forms of payment other than credit cards; in fact, we can define any payment term we want, and the engine will use whatever gateway is required. For example, we can split an invoice into 50% credit and 50% check. This is all taken care of by the new invoicing system - a major change in the rewrite which split finance from production. Previously, an order carried its own balance sheet. Now, we have orders and invoices, both acting independently. The level of administration was expanded as well...I added security profiles to the employee records. We have, at a minimum, page-level control over access, with granularity beyond that in several areas. For example, I can flag an employee as able to access the order manager, but not able to change any orders. They can look at the invoice ledger, but cannot post any payments.
We just pushed the new version of the site not even a month ago. You can see around when I pushed by when I started answering questions again on EE. (time is an ever-precious commodity) I'm still in the 'damage control' phase, which mainly consists of identifying bugs and crushing them as quickly as possible. As I mentioned, one of the bugs was in my pricing algorithm. It took me two days to find and affect a fix for it. As I did it, I lit upon an entirely new direction for the site. Let me make this clear - I JUST finished redesigning the entire site, for the SECOND time, and I just found a way to make 10x better. This is perhaps the most important lesson you can learn - projects are NEVER finished. There is always a way to make it better, or do more, or do it faster. I've been working on this project, and pretty much this project only, since Jan2006, and I could easily see myself working on it for the next 10 years and still not be anywhere near 'done'. As it is, I have my own personal development timeline which extends into the middle of two years from now, and that doesn't even consider whatever my company's goals end up being in that time.
It's a good thing this is my hobby, yes? :þ I hope this provides the insight you were looking for. If not, no problems...I like to write. I'm still compiling a list of my favorites "dos and don'ts" for your other question, but you can expect some of my input there as well. I've learned a lot over the past 2 years, and I don't mind sharing.
If you'd like to see some of this in action, contact me by email. I can't show you the actual code implementation, but I can give you a tour through the admin section of my development sandbox.
I started with the company in Dec2005. I was hired as the on-staff programmer. In reality, I'm the programmer, the designer, the server admin, the network guru, and local help desk. I'm one of two people in the entire company that knows anything about computers, and the other person functions as an HR administrator. Everything I.T. goes through me at some point. Most of it gets done by me directly. While I did work on other various things during most of my tenure to-date, I would say the majority of my time (6-12 hours a day on average) was dedicated to development of the new engine and underlying structure.
When I was hired, my primary responsibility was to keep up with the existing site. It had been programmed by someone else 2-1/2 years before I arrived, and it was in sorry condition. It looked like it had been coded by a third-grader. The database was in slightly better condition, but was severely limited by the MySQL version in which it was authored. For the first two months, I tried to tweak and revise as I could, but I saw too many structural problems that would prevent us from moving forward or expanding the site. At the very least, the old version of MySQL (3.23, IIRC) did not allow for sub-queries. As a result, the site dragged on the more complex operations. One page used to export order data to our production system took more than 20 minutes at times, and that has to be run every day at the start of business. I decided it was time for them to start from scratch, and began writing a brand new engine in Feb2006.
The first 'public' (publicly available, but not advertised, and not taking business) beta came 5 months later, in the middle of July. The beta consisted of being able to create an account and login, manipulate the cart in a variety of ways, and complete an order with a credit card. As the beta came up, the people who actually knew the products sent me configuration data. But I didn't know anything about the product, so configuring them was a lot like playing 'telephone'. The next month was dedicated to a versatile product manager so I could push that job to someone with knowledge. This included management of product-level fields, as well as option types, options, images, pricing charts, etc. At the same time, I created a basic order manager, and sample ordering mechanism. As I added functionality to the admin panel, I began tying all the different sections together. I made them modular so they could be called from anywhere, but each was its own little program. There were no classes yet...all of this was done strictly in PHP function-based code with the database holding data only.
Finally, near the end of October, the site actually went live. We had received a few orders (first month, we got 2!), and were able to give the system its first real, live, public testing. It was around this time I discovered a fatal flaw in my structure.
You have to realize that this is all coded by me, straight out of my head. If you go back through the few questions I posted here during that time, you'll see where I struggled with basic CSS concept and HTML portability. Even with the finished site, as it stands now with v2.0, there are only two sections of code that did not come entirely from me: an email class (still heavily edited by me to do certain things) and the captcha class (on my list for expansion when I get time). You can't ever account for all the problems you are bound to encounter, but good development is an exercise in catching the really bad ones before they become important. Sometimes you can find an error, and it only takes a day or two to back up and try again. In this instance, the flaw was in a basic concept of part of my structure. There was no hope for repair - a complete rewrite was necessary.
I started the second rewrite in Jan07. At first, the main concern was the custom CMS. We wanted to be able to supply SEO headers and meta tags to pages without editing code. I abstracted all of that back to the database. Now, every page goes through an 'overlord' page...a loader which is called mod_rewrite to assist in rendering the requested content. The loader calls the database, recognizes which PHP include files to use, which meta tags to write, etc. That change alone required converting every page on the site into the new format...breaking the pages so one half handles all the PHP, and the other handles only HTML and presentation. Since we were going to use the engine for more than one site, I had to be able to configure it easily to work with different rules. The table handling these differences tells the engine if the cart module should be available, if customer accounts are required, or if certain surcharges are added to the total. All of the PHP was modified to use this information. In the old version, pricing an item was done through an algorithm inside the page. My latest change (completed last night, btw) was to abstract that process into the database. This centralized my pricing algorithm into a procedure that could be called from anywhere, including remote sites. That will be important for us later when we want to provide remote pricing to franchises from our main product database, or maybe from an AJAX call for dynamic forms.
Centralization was a primary goal of the second rewrite. Previously, the code was all function-based. I had hundreds of functions and managing them was a serious issue. The first step of the rewrite was to upgrade to MySQL 5 and PHP 5. This opened up classes in PHP, and stored procedures (like the pricing algorithm) in MySQL. All of the functions were rewritten into classes. Code that used to be 100 lines to pull an order summary can now be accomplished in 4 or 5 instead. If I decide to change the way a particular item works, I only have to change it one place - the class that handles it.
All through the rewrite, I was not just 'fixing' existing items. I was also expanding our abilities. All of the CSS work I did not have time for before was completed...almost all style is now controlled by a company-defined style sheet. The account management system was expanded to allow for saving/editing address profiles, as well as basic quote/order management. We now have the ability to accept forms of payment other than credit cards; in fact, we can define any payment term we want, and the engine will use whatever gateway is required. For example, we can split an invoice into 50% credit and 50% check. This is all taken care of by the new invoicing system - a major change in the rewrite which split finance from production. Previously, an order carried its own balance sheet. Now, we have orders and invoices, both acting independently. The level of administration was expanded as well...I added security profiles to the employee records. We have, at a minimum, page-level control over access, with granularity beyond that in several areas. For example, I can flag an employee as able to access the order manager, but not able to change any orders. They can look at the invoice ledger, but cannot post any payments.
We just pushed the new version of the site not even a month ago. You can see around when I pushed by when I started answering questions again on EE. (time is an ever-precious commodity) I'm still in the 'damage control' phase, which mainly consists of identifying bugs and crushing them as quickly as possible. As I mentioned, one of the bugs was in my pricing algorithm. It took me two days to find and affect a fix for it. As I did it, I lit upon an entirely new direction for the site. Let me make this clear - I JUST finished redesigning the entire site, for the SECOND time, and I just found a way to make 10x better. This is perhaps the most important lesson you can learn - projects are NEVER finished. There is always a way to make it better, or do more, or do it faster. I've been working on this project, and pretty much this project only, since Jan2006, and I could easily see myself working on it for the next 10 years and still not be anywhere near 'done'. As it is, I have my own personal development timeline which extends into the middle of two years from now, and that doesn't even consider whatever my company's goals end up being in that time.
It's a good thing this is my hobby, yes? :þ I hope this provides the insight you were looking for. If not, no problems...I like to write. I'm still compiling a list of my favorites "dos and don'ts" for your other question, but you can expect some of my input there as well. I've learned a lot over the past 2 years, and I don't mind sharing.
If you'd like to see some of this in action, contact me by email. I can't show you the actual code implementation, but I can give you a tour through the admin section of my development sandbox.
ASKER
Awesome feedback, thanks so much. I tried to email and it keeps getting returned. I know it's @ not at and .com not "dot-com" obviously, but I'm not sure what I'm doing wrong on the rest of the email address listed on your profile. It does sound like your system has some similarities and I'd love to see whatever you're able to show me.
The email should just be my user name followed by domain.com. I put extra words in to discourage scraping.
ASKER
I just posted a question about junction tables:
https://www.experts-exchange.com/questions/22911595/Best-Method-for-Junction-Table.html
--I keep reading conflicting opinions and would love some input.
https://www.experts-exchange.com/questions/22911595/Best-Method-for-Junction-Table.html
--I keep reading conflicting opinions and would love some input.
Jim