Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
05-20-2009, 08:34 PM #1
- Join Date
- Nov 2008
- Thanked 4 Times in 4 Posts
Brainstorming: Possible multi-select query to single query?
Brief description of tables:
part table: id, nameid and ids of parts. if there are multiple sub-parts for a part, then the name appears multiple times, each time with an id of the sub-part. So it's easy to find the complete list of sub-parts for a part.
where 'a' is the auto-increment field for the part table
'b' is the part id
'c' is the name id.
So when a part is entered (eg. 'part1'), a list of sub-parts that make up that part is presented (assume 'part1' = 3, so 1 & 2 are presented, showing that subpart1 and subpart2 make up that part), then user can click on each sub-part to get all the information.
Now management want multiple part numbers which are exactly the same set of sub-parts. This is easy, we can extract sub-parts from a previous part which is setup and apply them to the new part:
However, in the future if another sub-part is added to a part, it will not be automatically added to the other parts which are identical is all but name.
Since these additional parts are basically just aliases of existing parts, we can create another table (lets call it aliases) which matches a name id to a part id (so we lookup partid4 directly), or one name to another name (nameid4=nameid3, then lookup nameid3 in the part table). partid lookups will be quicker as the auto incremented column is indexed and unique, and I can see some time in the future, management wanting a part to be the contents of another part plus some sub-parts.
So, lets ignore names for now, if we change the first type like so:
1,1,1 [sub part 1]
2,2,2 [sub part 2]
3,1,3 [part id 3 contains sub part 1]
4,2,3 [and also sub part 2]
5,5,4 [sub part 3]
6,3,5 [part id 6 contains part id 3]
7,5,5 [plus sub part 3]
That looks nasty doesn't it? Referencing an id that just points to other, and doesn't even include all of them.
I know I can solve this problem with multiple select queries and loops in php, but I really want as much as possible in the sql side and less eating cpu cycles parsing php code.
Any ideas from other people? I'm thinking there must be an easier way.
I can make slight changes to the database and structure, along with code alterations, but don't really want to rewrite the entire system to use it.
One last thing: The server is MySQL 4.1.
Last edited by scrappy; 05-21-2009 at 09:44 AM.
05-21-2009, 06:44 AM #2
Yeah, the classical "tree" problem.
And I think you goofed in your "tree" presentation. If "a" is an autonumber field, how'd you get duplicate values???
Let's rewrite that a bit more meaningfully. Let's use the partname in place of the nameid, for clarity. And if a part doesn't contain any other parts, try using NULL instead of duplicateing the autoid field:
Do I have that right??Code:autoid :: contains :: partname 1 :: null :: subpartA 2 :: null :: subpartB 3 :: 1 :: partX [partX contains subpartA] 4 :: 2 :: partX [partX contains subpartB] 5 :: null :: subpartC 6 :: 3 :: partY [partY contains subpartB] 7 :: 5 :: partY [partY contains subpartC]
Now a question: With this system, I don't see any way to have sub-sub parts. Is that okay? Is that intended??
There *are* other ways to organize the data so that you can have multi-level trees, but I don't see a way to do that with this system.
Is it possible that you were trying to say that your column "a" is a *FOREIGN* key to another table with part number/names???
Hmmm...but then what's the point of your column "c"???
I'm a bit lost.
05-21-2009, 09:56 AM #3
- Join Date
- Nov 2008
- Thanked 4 Times in 4 Posts
Good idea. That would make more sense.if a part doesn't contain any other parts, try using NULL instead of duplicateing the autoid field:
Yes, perfect.Do I have that right??
At the moment, yes. However, management already want aliases, so they can have parts where the only difference is the name of the part. I can see not too long in the future for them to want sub-sub parts. I don't see it going any further than that however (sub-sub-sub is likely never happen).Now a question: With this system, I don't see any way to have sub-sub parts. Is that okay? Is that intended??
Neither do I, so I appreciate your comments on how you would go about doing such a tree system.There *are* other ways to organize the data so that you can have multi-level trees, but I don't see a way to do that with this system.
No, column A is simple auto-number, I just goofed when I typed the table up above. Column C is indeed the id of the name.Is it possible that you were trying to say that your column "a" is a *FOREIGN* key to another table with part number/names???
05-21-2009, 10:22 PM #4
Okay, for starters, I'd just ditch the autonumber column.
It isn't doing anything useful and I can't see any query that would ever depend on it.
And then I'd just have the table look like:
Where *both* those id's refer into the "part names" table.Code:partID : subPartID
Again, for clarity, I'm going to use names in my example table instead of numbers.
So that means the partXXX is reallyCode:TABLE NAME: assemblies partID : subpartID partX : partA partX : partB partY : partB partY : partC partXXX : partD partXXX : partY
That is, we now handle sub-sub parts.Code:partXXX partD partY partB partC
And now parts that are *ONLY* used as sub-parts are *NOT* shown in this table.
If they need to kept in another table (for what??), fine. But they don't appear in this table.
To find all the sub-parts *AND* sub-sub-parts that make up one assembly, your query looks like this:
For the above table (and again using names instead of ids) that would give us:Code:SELECT 1 as ordering, partID, subPartID FROM assemblies UNION SELECT 2, a1.partID, a2.subPartID FROM assemblies AS a1, assemblies AS a2 WHERE a1.subpartID = a2.partID ORDER BY partID, ordering, subpartID
And, yes, it could easily be extended to sub-sub-sub parts. Past about 4 levels, though, I'd go with a different scheme.Code:1 :: partX :: partA 1 :: partX :: partB 1 :: partY :: partB 1 :: partY :: partC 1 :: partXXX :: partD 1 :: partXXX :: partY 2 :: partXXX :: partB 2 :: partXXX :: partC
Users who have thanked Old Pedant for this post: