little OT: DB design question

microsoft.public.dotnet.framework.adonet

29 Jun 2006 6:11 PM

sklett

I'm pretty new to DB design and wanted to run a question by you all.
I have normalized my schema to level 3 (I think) and I'm now finding that I
need to do many joins to bring all my different data together to model an
actual real world entity.

Here are some tables for example:

CREATE TABLE tbl_bm_builds (
    ID            int(11) AUTO_INCREMENT NOT NULL,
    DateCreated   date NOT NULL DEFAULT '0001-01-01',
    Notes         varchar(1500) NOT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE tbl_bm_builditems (
    ID           int(11) AUTO_INCREMENT NOT NULL,
    BuildID      int(11) NOT NULL DEFAULT '0',
    CustomerID   int(11) NOT NULL DEFAULT '0',
    DeviceID     int(11) NOT NULL DEFAULT '0',
    BoardSnStart int(11) NOT NULL DEFAULT '0',
    BoardSnEnd   int(11) NOT NULL DEFAULT '0',
    Quantity     int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY(ID)
)

CREATE TABLE tbl_devices (
    DeviceID    int(11) NOT NULL,
    ProductName varchar(50) NULL,
    PRIMARY KEY(DeviceID)
)

CREATE TABLE tbl_customers (
    CustomerID int(11) AUTO_INCREMENT NOT NULL,
    CompanyName varchar(50) NOT NULL,
    CustCode    varchar(10) NOT NULL,
    FirmwareID int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY(CustomerID)
)

Let's say I want to retreive data such as customer, build, device
information, etc from a device serial number, currently I need to do
something like this:
CREATE OR REPLACE VIEW `pmddirect`.`vw_bm_DeviceInfo` AS
SELECT
    # DEVICE INFORMATION
    d.SN AS DeviceSN,
    d.ProductSN AS ProductSN,

    # DEVICE TYPE INFORMATION
    dt.Name,
    dt.NumChannels,
    dt.MaxAmplitude,

    # CUSTOMER INFORMATION
    cust.FirmwareID,
    cust.CompanyName,

    # BUILD INFORMATION
    b.DateCreated AS BuildCreated,
    (b.DateCompleted > b.DateCreated) AS ActiveBuild

    # TEST RESULT INFORMATION

FROM tbl_bm_Devices AS d
INNER JOIN tbl_bm_DeviceTypes AS dt ON dt.TypeID = d.TypeID
INNER JOIN tbl_bm_BuildItems AS bi ON bi.ID = d.BuildItemID
INNER JOIN tbl_customers AS cust ON cust.CustomerID = bi.CustomerID
INNER JOIN tbl_bm_Builds AS b ON b.ID = bi.BuildID;

My question is: Is it bad design to have multiple routes to link or join
related tables? In other words, currently to get the customer information
from a serial number I take this path:
[SN] -> tbl_Devices-> tbl_bm_BuildItem-> tbl_bm_Builds-> tbl_Customer

If I were to add an FK 'CustomerID' to my tbl_devices I could just do this:
[SN]-> tbl_Devices-> tbl_Customer

Simpler. But I feel like it might be a bad design? Is it a "best practice"
to have 1 logical path to join related tables? It would seem that if you
didn't have such a rule, you could end up with a spiderweb of relations and
links between tables.

In case you are sick to your stomach from my schema design, let me try and
explain:

A 'Build' has 'BuildItems'
a 'BuildItem' defines a quantity of 'Devices' specific to a 'Customer', the
Serial Number range for those items and a few other properties
a 'Device' represents a single, serial numbered device

I think I may be a little over my head, if anyone has any pointers, please
feel free to fire away.

Thanks for reading,
Steve Klett

30 Jun 2006 6:32 AM

Cor Ligthert [MVP]

Steve,

A simple answer, in ADONET is working with joins a hell for updating,
therefore you can better try it with relations and than get more datatables.
(And use Join where you want only to show data).

As well don't use the auto increment if you can and want to use ADONET, use
the uniqueidentifier with Guid's.

This question is not OT in this newsgroup by the way.

Just my thought,

Cor

Show quote

"sklett" <skl***@mddirect.com> schreef in bericht
news:OIChje6mGHA.2360@TK2MSFTNGP04.phx.gbl...
> I'm pretty new to DB design and wanted to run a question by you all.
> I have normalized my schema to level 3 (I think) and I'm now finding that
> I need to do many joins to bring all my different data together to model
> an actual real world entity.
>
> Here are some tables for example:
>
> CREATE TABLE tbl_bm_builds (
>    ID            int(11) AUTO_INCREMENT NOT NULL,
>    DateCreated   date NOT NULL DEFAULT '0001-01-01',
>    Notes         varchar(1500) NOT NULL,
>    PRIMARY KEY(ID)
> )
>
>
> CREATE TABLE tbl_bm_builditems (
>    ID           int(11) AUTO_INCREMENT NOT NULL,
>    BuildID      int(11) NOT NULL DEFAULT '0',
>    CustomerID   int(11) NOT NULL DEFAULT '0',
>    DeviceID     int(11) NOT NULL DEFAULT '0',
>    BoardSnStart int(11) NOT NULL DEFAULT '0',
>    BoardSnEnd   int(11) NOT NULL DEFAULT '0',
>    Quantity     int(11) NOT NULL DEFAULT '0',
>    PRIMARY KEY(ID)
> )
>
>
>
> CREATE TABLE tbl_devices (
>    DeviceID    int(11) NOT NULL,
>    ProductName varchar(50) NULL,
>    PRIMARY KEY(DeviceID)
> )
>
>
> CREATE TABLE tbl_customers (
>    CustomerID int(11) AUTO_INCREMENT NOT NULL,
>    CompanyName varchar(50) NOT NULL,
>    CustCode    varchar(10) NOT NULL,
>    FirmwareID int(11) NOT NULL DEFAULT '0',
>    PRIMARY KEY(CustomerID)
> )
>
>
>
>
> Let's say I want to retreive data such as customer, build, device
> information, etc from a device serial number, currently I need to do
> something like this:
> CREATE OR REPLACE VIEW `pmddirect`.`vw_bm_DeviceInfo` AS
> SELECT
>    # DEVICE INFORMATION
>    d.SN AS DeviceSN,
>    d.ProductSN AS ProductSN,
>
>
>    # DEVICE TYPE INFORMATION
>    dt.Name,
>    dt.NumChannels,
>    dt.MaxAmplitude,
>
>    # CUSTOMER INFORMATION
>    cust.FirmwareID,
>    cust.CompanyName,
>
>    # BUILD INFORMATION
>    b.DateCreated AS BuildCreated,
>    (b.DateCompleted > b.DateCreated) AS ActiveBuild
>
>
>    # TEST RESULT INFORMATION
>
> FROM tbl_bm_Devices AS d
> INNER JOIN tbl_bm_DeviceTypes AS dt ON dt.TypeID = d.TypeID
> INNER JOIN tbl_bm_BuildItems AS bi ON bi.ID = d.BuildItemID
> INNER JOIN tbl_customers AS cust ON cust.CustomerID = bi.CustomerID
> INNER JOIN tbl_bm_Builds AS b ON b.ID = bi.BuildID;
>
>
>
>
>
>
> My question is: Is it bad design to have multiple routes to link or join
> related tables? In other words, currently to get the customer information
> from a serial number I take this path:
> [SN] -> tbl_Devices-> tbl_bm_BuildItem-> tbl_bm_Builds-> tbl_Customer
>
> If I were to add an FK 'CustomerID' to my tbl_devices I could just do
> this:
> [SN]-> tbl_Devices-> tbl_Customer
>
> Simpler. But I feel like it might be a bad design? Is it a "best
> practice" to have 1 logical path to join related tables? It would seem
> that if you didn't have such a rule, you could end up with a spiderweb of
> relations and links between tables.
>
> In case you are sick to your stomach from my schema design, let me try and
> explain:
>
> A 'Build' has 'BuildItems'
> a 'BuildItem' defines a quantity of 'Devices' specific to a 'Customer',
> the Serial Number range for those items and a few other properties
> a 'Device' represents a single, serial numbered device
>
> I think I may be a little over my head, if anyone has any pointers, please
> feel free to fire away.
>
> Thanks for reading,
> Steve Klett
>

30 Jun 2006 10:30 AM

Earl

While I don't see a database question as being off-topic for ADO (.Net or
otherwise), you would certainly get much more in-depth answers in the
microsoft.public.sqlserver.programming forum. Be forewarned: don your
asbestos firesuit, as those folks are probably the most technically
demanding newsgroup around (think of the law professor in Paper Chase). But
if you want honest and expansive answers, then post it over there.
Incidentally, you can figure out if you are normalized to 3NF simply by
ensuring that your database meets the requirements for 3NF. Work through the
process, 1NF, 2NF, 3NF. But the short answer to your question is that the
more normalized your database, *usually* the more joins you will have to
retrieve data. But, as Cor has pointed out, in ADO.Net, you are usually
better off to NOT join (except for reports), to retrieve your tables
individually, then make the "join" with a data relation.

Show quote

"sklett" <skl***@mddirect.com> wrote in message
news:OIChje6mGHA.2360@TK2MSFTNGP04.phx.gbl...
> I'm pretty new to DB design and wanted to run a question by you all.
> I have normalized my schema to level 3 (I think) and I'm now finding that
> I need to do many joins to bring all my different data together to model
> an actual real world entity.
>
> Here are some tables for example:
>
> CREATE TABLE tbl_bm_builds (
>    ID            int(11) AUTO_INCREMENT NOT NULL,
>    DateCreated   date NOT NULL DEFAULT '0001-01-01',
>    Notes         varchar(1500) NOT NULL,
>    PRIMARY KEY(ID)
> )
>
>
> CREATE TABLE tbl_bm_builditems (
>    ID           int(11) AUTO_INCREMENT NOT NULL,
>    BuildID      int(11) NOT NULL DEFAULT '0',
>    CustomerID   int(11) NOT NULL DEFAULT '0',
>    DeviceID     int(11) NOT NULL DEFAULT '0',
>    BoardSnStart int(11) NOT NULL DEFAULT '0',
>    BoardSnEnd   int(11) NOT NULL DEFAULT '0',
>    Quantity     int(11) NOT NULL DEFAULT '0',
>    PRIMARY KEY(ID)
> )
>
>
>
> CREATE TABLE tbl_devices (
>    DeviceID    int(11) NOT NULL,
>    ProductName varchar(50) NULL,
>    PRIMARY KEY(DeviceID)
> )
>
>
> CREATE TABLE tbl_customers (
>    CustomerID int(11) AUTO_INCREMENT NOT NULL,
>    CompanyName varchar(50) NOT NULL,
>    CustCode    varchar(10) NOT NULL,
>    FirmwareID int(11) NOT NULL DEFAULT '0',
>    PRIMARY KEY(CustomerID)
> )
>
>
>
>
> Let's say I want to retreive data such as customer, build, device
> information, etc from a device serial number, currently I need to do
> something like this:
> CREATE OR REPLACE VIEW `pmddirect`.`vw_bm_DeviceInfo` AS
> SELECT
>    # DEVICE INFORMATION
>    d.SN AS DeviceSN,
>    d.ProductSN AS ProductSN,
>
>
>    # DEVICE TYPE INFORMATION
>    dt.Name,
>    dt.NumChannels,
>    dt.MaxAmplitude,
>
>    # CUSTOMER INFORMATION
>    cust.FirmwareID,
>    cust.CompanyName,
>
>    # BUILD INFORMATION
>    b.DateCreated AS BuildCreated,
>    (b.DateCompleted > b.DateCreated) AS ActiveBuild
>
>
>    # TEST RESULT INFORMATION
>
> FROM tbl_bm_Devices AS d
> INNER JOIN tbl_bm_DeviceTypes AS dt ON dt.TypeID = d.TypeID
> INNER JOIN tbl_bm_BuildItems AS bi ON bi.ID = d.BuildItemID
> INNER JOIN tbl_customers AS cust ON cust.CustomerID = bi.CustomerID
> INNER JOIN tbl_bm_Builds AS b ON b.ID = bi.BuildID;
>
>
>
>
>
>
> My question is: Is it bad design to have multiple routes to link or join
> related tables? In other words, currently to get the customer information
> from a serial number I take this path:
> [SN] -> tbl_Devices-> tbl_bm_BuildItem-> tbl_bm_Builds-> tbl_Customer
>
> If I were to add an FK 'CustomerID' to my tbl_devices I could just do
> this:
> [SN]-> tbl_Devices-> tbl_Customer
>
> Simpler. But I feel like it might be a bad design? Is it a "best
> practice" to have 1 logical path to join related tables? It would seem
> that if you didn't have such a rule, you could end up with a spiderweb of
> relations and links between tables.
>
> In case you are sick to your stomach from my schema design, let me try and
> explain:
>
> A 'Build' has 'BuildItems'
> a 'BuildItem' defines a quantity of 'Devices' specific to a 'Customer',
> the Serial Number range for those items and a few other properties
> a 'Device' represents a single, serial numbered device
>
> I think I may be a little over my head, if anyone has any pointers, please
> feel free to fire away.
>
> Thanks for reading,
> Steve Klett
>