little OT: DB design question

  • Thread starter Thread starter sklett
  • Start date Start date
S

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
 
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

sklett said:
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
Click to expand...
 
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.

sklett said:
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
Click to expand...
 
Back
Top