Prev: How Can I establish connection 16bit o.s MS DOS 6.22 to to SQL Se
Next: Logshipping from SQL2000
From: Kurt Hill on 27 Feb 2010 15:36
This is a design question. Here's hoping Joe Celko haunts this newsgroup ;)

The relevant DDL will be at the bottom of the post.

We have two entities -- sporting events and "product" which could be a cup,
shirt or a fee. Obviously, events have a number of attributes not needed
for "product". The natural PK for events is (gameid, sportid, class,
eventnum). The end user would see a combined form of the this as a "product
code", such as "ARA01" (The game ID is does not need to be shown). Products
do not have all the various medical,
teamtype etc. flags, but share the same code-form. An example would be a
blazer, perhaps coded as "BZB01" (e.g., Blazer, blue, size 1). My current
model is an event is as a subtype of a product.

NOTE: Please ignore the PK declared in the DDL, I'm talking about the
natural PK...

One issue is that the particular parts of the event code -- mean
something -- sport code is an FK to a sports table, so for the events table
these are individual attributes. For products though, there are no
subcomponents to the name, so no need to have three separate fields. At the
application level products and events are the same so it's important to that
they are of the same format and do not collide.

The core of my question is: By putting a complete "product code" in the
products table, I am at risk for having the product code get out of sync
with the products table. Another way of saying this is that my PK is
duplicated in condensed form in the Products table and I don't like it. I
currently have GUID's as PKs which I also hate with a passion that spans all
of space and time in it's infinite umbrage...

If I combine these types into one table, then many fields will need to be
nullable as they have no meaning for "products", and I would also have to
leave the key decomposed (sportid, classid, eventum) for products which
would not work -- there would be no corresponding "BZ" (blazer) in the
sports table for instance.

I'm in the design stage, so this is the first, best chance I have to really
roger things up...






CREATE TABLE [dbo].[Events](
[gidEventID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[chGameID] [char](6) NOT NULL,
[chSportID] [char](2) NOT NULL,
[chClass] [char](1) NOT NULL,
[chEventNum] [char](2) NOT NULL,
[chTeamType] [char](1) NOT NULL,
[chSex] [char](1) NOT NULL,
[blnGetScore] [bit] NOT NULL,
[blnGetMedical] [bit] NOT NULL,
[blnAllowPool] [bit] NOT NULL,
[blnAllowTechnicalSupport] [bit] NOT NULL,
[blnRequiresFirearm] [bit] NOT NULL,
[intMinAge] [smallint] NULL,
[intLimit] [smallint] NULL,
CONSTRAINT [PK_Events] PRIMARY KEY NONCLUSTERED
(
[gidEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Products](
[chGameID] [char](6) NOT NULL,
[chProductCode] [char](5) NOT NULL,
[vchDescription] [varchar](50) NOT NULL,
[curTotalFee] [smallmoney] NOT NULL,
[curCpafFee] [smallmoney] NOT NULL,
[curHostFee] [smallmoney] NOT NULL,
[gidEventID] [uniqueidentifier] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[chGameID] ASC,
[chProductCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



 | 
Pages: 1
Prev: How Can I establish connection 16bit o.s MS DOS 6.22 to to SQL Se
Next: Logshipping from SQL2000