...

View Full Version : Database Structure Help!



masterz2k
11-12-2011, 10:23 AM
Hello All...

Just wanted some advice...

I have some Data on PartNumber supercessions which i'd like to store for future use

data is essentaly...

Part1 --> Part2 -->Part3 -->Part4

length of chain varies..

I want to be able to store this... but also highlight the "latest" Part Number..

so essentialy im able to query my database.. say, Part2, the answer i end up with is Part4


im thinking..
Table.

ChainID / Part / Latest
001 Part1
001 Part2
001 Part3
001 Part4 Y


essentialy i could query the Part - get the chainID, then get the latest Part? (y)


does this structure make sense - or is there a more effective way of storing

this will eventualy contain around 200k part numbers, say 75k "chains"

Old Pedant
11-12-2011, 10:38 AM
I don't understand what makes "Part4" be the "Latest".

Or are you saying that "Latest" *is* a field in the DB?

If so, then your table structure is just fine. You don't have to "query the Part".

Just do:


SELECT T2.* FROM table AS T1, table AS T2
WHERE T1.ChainID = T2.ChainID
AND T1.Part = 'Part2'
AND T2.Latest = 'Y'

Yes, it works even if you are looking for 'Part4'.

Just a simple "self join".

Naturally, you would need ChainID to be indexed for decent performance. And you'd probably want Part to be indexed as well.

If all the values in Part for a given ChainID are different, then in fact you could make the combination of ChainID and Part be your primary key, and then it would be really efficient.

Come to think of it, even if they aren't unique, you still might get better performance using a non-unique composite key, combiing ChainID and Part.

Old Pedant
11-12-2011, 10:43 AM
Comment: If you ever might want to be able to reconstruct the ordering of your chain, you might consider using


ChainID / Part / Sequence
001 / Part1 / 1
001 / Part2 / 2
001 / Part3 / 3
001 / Part4 / 4

And then your query becomes:


SELECT T.*
FROM table AS T,
( SELECT ChainID, MAX(Sequence) AS MaxSequence
FROM table
GROUP BY ChainID ) AS S
WHERE T.ChainID = S.ChainID
AND T.Part = 'Part2'
AND T.Sequence = S.MaxSequence

Hmmm...SQL Server could optimize that, but I'm not so sure about MySQL.

Let me think on it.

masterz2k
11-12-2011, 10:44 AM
Hi,

Thanks for the Tips re the Primary ID.

Do you think there is anyway building in a way of tracking the order of the parts?

Latest was going to be an ID in the table.

or, could i change latest to a number,

ie

ChainID / Part / Latest
001 Part1 1
001 Part2 2
001 Part3 3
001 Part4 4


then modify the query to pick the highest value in "latest" against the chain ID?

Old Pedant
11-12-2011, 10:48 AM
I think for MySQL using Sequence, if you wanted decent performance, you'd have to do this:


SELECT T.*
FROM table AS T,
( SELECT T2.ChainID, MAX(T2.Sequence) AS MaxSequence
FROM table AS T1, table AS T2
WHERE T1.ChainID = T2.ChainID
AND T1.Part = 'Part2'
GROUP BY T2.ChainID ) AS S
WHERE T.ChainID = S.ChainID
AND T.Sequence = S.MaxSequence

Which looks more complex and looks like it would be slower, but it won't be.

See, the inner SELECT would immediately find that it only need (in our example) 4 records (the ones with ChainID = 001) and so then it just has to select the MAX from those 4. And then the join to the main T table is lightning fast, because it has already fetch that record as part of the inner SELECT!

Old Pedant
11-12-2011, 10:50 AM
Do you think there is anyway building in a way of tracking the order of the parts?


LOL! Beat you to it!!

Incidentally, if you used an auto_increment ID for the entire table *AND* the parts were entered in sequence order, then yes, you could use that in place of the Sequence field.

masterz2k
11-12-2011, 11:58 AM
Thanks for the Help! :) autoID would be a good way i think :)

i'l have a play with getting the data into the correct format and a way of keeping it updated / amended :)

Sequence wouldn't be needed immediately, but i like to plan ahead to in the future it might well be needed, the AutoID would future proof this as well as keeping the "latest" easier, infact makes the maintenance easier as well :) as anything added would automacicaly be the latest... as long as the starting data was OK!

Old Pedant
11-12-2011, 11:15 PM
Actually, adding another record with same ChainID but a one-greater sequence number isn't that hard.

Example:


INSERT INTO table ( ChainID, Part, Sequence )
SELECT ChainID, 'new part name', MAX(Sequence) + 1
FROM table
WHERE ChainID = $chosenchainid
GROUP BY ChainID

Or even


INSERT INTO table ( ChainID, Part, Sequence )
SELECT ChainID, 'new part name', Sequence + 1
FROM table
WHERE ChainID = $chosenchainid
ORDER BY Sequence DESC LIMIT 1



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum