database design
in [UK Motorcycles]
|
From: doetnietcomputeren on 10 Feb 2010 02:47 On 2010-02-10 08:30:51 +0100, Pip Luscher <pluscher(a)live.invalid.co.uk> said: > At work, as a result of merging two vastly differing production > systems, we've got a requirement for production line servers to keep > track of a second serial number on some but not all products: > effectively a software serial number as well as the box's hardware > serial number. This has to be linked to the box's hardware number > (which is on a label on the box) so that it can be identified under, > say, warranty conditions. > > As I'm no database guru, what's /technically/ the best way to do this: Surely that depends on what you need to do with the data, later down the line? > add a column to the existing table that already stores the hardware > serial number, This runs the risk of corrupting existing data, should your testing be insufficent to spot a problem. > or add a whole new table that links the two? This can be implemented more easily and is safer in a production environment install. > Or is it > simply a matter of horses for courses? I may simply opt for whatever's > easiest to implement but I would like to have an idea of what the best > practrce is. Me either - my former lot had both types of scenario. -- Dnc
From: Buzby on 10 Feb 2010 02:49 Pip Luscher wrote: > At work, as a result of merging two vastly differing production > systems, we've got a requirement for production line servers to keep > track of a second serial number on some but not all products: > effectively a software serial number as well as the box's hardware > serial number. This has to be linked to the box's hardware number > (which is on a label on the box) so that it can be identified under, > say, warranty conditions. > > As I'm no database guru, what's technically the best way to do this: > add a column to the existing table that already stores the hardware > serial number, or add a whole new table that links the two? Or is it > simply a matter of horses for courses? I may simply opt for whatever's > easiest to implement but I would like to have an idea of what the best > practrce is. > > And no, not doing this and redesigning the serial number system is not > an option at present. Nor is GAMI. > > Database system is MySQL, if that makes a difference. > > Thanks for any pointers. If it's only one serial number you want to track I'd have thought if you already have all the harware numbers in a table the easiest option would be to add an additional column. If it's more than one then an additional table. -- Buzby "There's nothing more dangerous than a resourceful idiot"
From: Catman on 10 Feb 2010 03:22 Pip Luscher wrote: > At work, as a result of merging two vastly differing production > systems, we've got a requirement for production line servers to keep > track of a second serial number on some but not all products: > effectively a software serial number as well as the box's hardware > serial number. This has to be linked to the box's hardware number > (which is on a label on the box) so that it can be identified under, > say, warranty conditions. > > As I'm no database guru, what's /technically/ the best way to do this: > add a column to the existing table that already stores the hardware > serial number, or add a whole new table that links the two? IME[1] this one. [1] I am no DBA, but I used to run the DBA teams, and this was accepted SOP when the developers needed to add more data in similar situations[2] [2] No doubt there will now be hundreds of UKRMers queueing up to tell us we shouldn't have been in business, let alone allowed a database if we were developing like that[3] etc etc [3] Well it *is* UKRM ;) -- Catman MIB#14 SKoGA#6 TEAR#4 BOTAFOF#38 Apostle#21 COSOC#3 Tyger, Tyger Burning Bright (Remove rust to reply) 116 Giulietta 3.0l Sprint 1.7 GTV TS GT 3.2 V6 Triumph Sprint ST 1050: It's blue, see. www.cuore-sportivo.co.uk
From: darsy on 10 Feb 2010 03:25 On Feb 10, 7:30 am, Pip Luscher <plusc...(a)live.invalid.co.uk> wrote: > Thanks for any pointers. no, don't do that; just add another column and index it. -- d.
From: Jeremy on 10 Feb 2010 03:52
In article <46n4n51bv1mmdsi80vldus32nlkm163jvs(a)4ax.com>, pluscher(a)live.invalid.co.uk says... > As I'm no database guru, what's /technically/ the best way to do this: > add a column to the existing table that already stores the hardware > serial number, or add a whole new table that links the two? Or is it > simply a matter of horses for courses? I may simply opt for whatever's > easiest to implement but I would like to have an idea of what the best > practrce is. > > If the new item of data has not other "attributes" and is strictly related to one and only one hardware serial# then an additional column is the correct solution. Creating an additional table with a PK of the hardware serial# would be pointless just to hold the corresponding software serial# -- jeremy K1200S |