Need a little assistance in table design please.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need a little assistance in table design please.

Author

Topic

wiltech
Yak Posting Veteran

51 Posts

Posted - 2007-10-19 : 15:16:24

I'm gettting a bit stumped here, I have the database laid out to get what most of I need accomplished...

I can get a list of ALL of the logos that a product qualifies for, but not the most "covering" one(s).


CREATE TABLE [dbo].[Hybrids] (
	[HybridID] [int] NOT NULL ,
	[InbredIDMale] [int] NULL ,
	[InbredIDFemale] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InbredTraits] (
	[InbredID] [int] NOT NULL ,
	[TraitID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Inbreds] (
	[InbredID] [int] NOT NULL ,
	[InbredDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LogoTraits] (
	[LogoID] [int] NOT NULL ,
	[TraitID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Logos] (
	[LogoID] [int] NOT NULL ,
	[LogoDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Hybrids] ADD 
	CONSTRAINT [PK_Hybrids] PRIMARY KEY  CLUSTERED 
	(
		[HybridID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[InbredTraits] ADD 
	CONSTRAINT [PK_InbredTraits] PRIMARY KEY  CLUSTERED 
	(
		[InbredID],
		[TraitID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Inbreds] ADD 
	CONSTRAINT [PK_Inbreds] PRIMARY KEY  CLUSTERED 
	(
		[InbredID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LogoTraits] ADD 
	CONSTRAINT [PK_LogoTraits] PRIMARY KEY  CLUSTERED 
	(
		[LogoID],
		[TraitID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Logos] ADD 
	CONSTRAINT [PK_Logos] PRIMARY KEY  CLUSTERED 
	(
		[LogoID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Hybrids] ADD 
	CONSTRAINT [FK_Hybrids_Inbreds] FOREIGN KEY 
	(
		[InbredIDMale]
	) REFERENCES [dbo].[Inbreds] (
		[InbredID]
	),
	CONSTRAINT [FK_Hybrids_Inbreds1] FOREIGN KEY 
	(
		[InbredIDFemale]
	) REFERENCES [dbo].[Inbreds] (
		[InbredID]
	)
GO

ALTER TABLE [dbo].[InbredTraits] ADD 
	CONSTRAINT [FK_InbredTraits_Inbreds] FOREIGN KEY 
	(
		[InbredID]
	) REFERENCES [dbo].[Inbreds] (
		[InbredID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_InbredTraits_Traits] FOREIGN KEY 
	(
		[TraitID]
	) REFERENCES [dbo].[Traits] (
		[TraitID]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[LogoTraits] ADD 
	CONSTRAINT [FK_LogoTraits_Logos] FOREIGN KEY 
	(
		[LogoID]
	) REFERENCES [dbo].[Logos] (
		[LogoID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_LogoTraits_Traits] FOREIGN KEY 
	(
		[TraitID]
	) REFERENCES [dbo].[Traits] (
		[TraitID]
	) ON DELETE CASCADE 

INSERT INTO Logos (LogoID, LogoDesc) VALUES (1, 'CBLL')
INSERT INTO Logos (LogoID, LogoDesc) VALUES (2, 'GT')
INSERT INTO Logos (LogoID, LogoDesc) VALUES (3, 'RW')
INSERT INTO Logos (LogoID, LogoDesc) VALUES (4, '4GT')
INSERT INTO Logos (LogoID, LogoDesc) VALUES (5, 'GTRW')

INSERT INTO Traits (TraitID, TraitDesc) VALUES (1, 'CB')
INSERT INTO Traits (TraitID, TraitDesc) VALUES (2, 'LL')
INSERT INTO Traits (TraitID, TraitDesc) VALUES (3, 'GA21')
INSERT INTO Traits (TraitID, TraitDesc) VALUES (4, 'RW')

INSERT INTO Inbreds (InbredID, InbredDesc) VALUES (1, 'Inbred1-CBLL')
INSERT INTO Inbreds (InbredID, InbredDesc) VALUES (2, 'Inbred2-GTRW')
INSERT INTO Inbreds (InbredID, InbredDesc) VALUES (3, 'Inbred3')
INSERT INTO Inbreds (InbredID, InbredDesc) VALUES (4, 'Inbred4-RW')

INSERT INTO LogoTraits (LogoID, TraitID) VALUES (1, 1)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (1, 2)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (2, 3)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (3, 4)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (4, 1)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (4, 2)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (4, 3)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (4, 4)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (5, 3)
INSERT INTO LogoTraits (LogoID, TraitID) VALUES (5, 4)

INSERT INTO InbredTraits (InbredID, TraitID) VALUES (1, 1)
INSERT INTO InbredTraits (InbredID, TraitID) VALUES (1, 2)
INSERT INTO InbredTraits (InbredID, TraitID) VALUES (2, 3)
INSERT INTO InbredTraits (InbredID, TraitID) VALUES (2, 4)
INSERT INTO InbredTraits (InbredID, TraitID) VALUES (4, 4)

INSERT INTO Hybrids (HybridID, InbredIDMale, InbredIDFemale) VALUES (1, 1, 2)
INSERT INTO Hybrids (HybridID, InbredIDMale, InbredIDFemale) VALUES (2, 1, 3)
INSERT INTO Hybrids (HybridID, InbredIDMale, InbredIDFemale) VALUES (3, 3, 2)
INSERT INTO Hybrids (HybridID, InbredIDMale, InbredIDFemale) VALUES (4, 4, 1)

In this example:


SELECT DISTINCT h.HybridID, 
	lt.LogoID, l.LogoDesc 
FROM Hybrids h
	INNER JOIN Inbreds i ON h.InbredIDMale = i.InbredID
		or h.InbredIDFemale = i.InbredID
	INNER JOIN InbredTraits it ON i.InbredID = it.InbredID
	INNER JOIN Traits t ON it.TraitID = t.TraitID
	INNER JOIN LogoTraits lt ON t.TraitID = lt.TraitID
	INNER JOIN (
		SELECT LogoID, COUNT(TraitID) as TotalTraits 
		FROM LogoTraits
		GROUP BY LogoID) tc ON lt.LogoID = tc.LogoID
	INNER JOIN Logos l ON lt.LogoID = l.LogoID
GROUP BY h.HybridID, lt.LogoID, tc.TotalTraits, l.LogoDesc
HAVING COUNT(lt.TraitID) = tc.TotalTraits

I only want to return:


HybridID    LogoID      LogoDesc                                           
----------- ----------- -------------------------------------------------- 
1           4           3000GT
2           1           CBLL
3           5           RWGT
4           1           CBLL
4           3           RW

I'm pretty sure that I need to add another table to define the heirachy of the logos, What would you suggest? And how would I use it to get the necessary results ?

Thanks
Tony W

wiltech
Yak Posting Veteran

51 Posts

Posted - 2007-10-24 : 10:09:05

Any suggestions, or does anyone need any additional information ?

Thanks
Tony W

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-24 : 10:13:14

You did not provide a definition of "most "covering" one(s)". What are you trying to do?

CODO ERGO SUM

wiltech
Yak Posting Veteran

51 Posts

Posted - 2007-10-24 : 10:44:29

quote:
Originally posted by Michael Valentine Jones

You did not provide a definition of "most "covering" one(s)". What are you trying to do?

CODO ERGO SUM

Sorry, i'll try my best to explain it as it was explained to me.

I need to show the most appropriate logo for a given hybrid, based on the "sum of the" genetic traits of it's inbreds. Each logo has specific traits, and some of those overlap.

ex:
The CBLL logo requires the CB & LL traits.
The GT logo requires the GA21 trait only.
The GTRW logo requires the GA21 & RW traits.
The 4GT logo requires the CB, LL, GA21, and RW traits.

So if a hybrid has all 4 traits, even though it technically qualifies for all 4 logos we only want to list the top one, in this case it's 4GT.

If a hybrid has CB, LL & RW, we should list 2 logos, the CBLL & RW.

Does this make sense?

I could hard code this into the GUI (which is where it is now) but every year we have a new crop of trademarked traits, and it's becoming a nightmare to maintain.

Thanks
Tony W

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources