quote:

---

Originally posted by rockmoose

42 tables is not normalized ?

---

create table [section]([section] char(5) primary key)
create table [subsection]([subsection] char(1) primary key)
create table [number]([number] int identity primary key, x bit)
create table thetable([section] char(5),[subsection] char(1),[number] int, blah varchar(40) primary key clustered([section],[subsection],[number]))
GO

insert [section]([section]) select 'one' union select 'two' union select 'three' union select 'four' union select 'five' union select 'six'
insert [subsection]([subsection]) select 'a' union select 'b' union select 'c' union select 'd' union select 'e' union select 'f'
insert [number](x) select top 500 1 from master.dbo.spt_values

insert thetable([section],[subsection],[number],blah)
select [section],[subsection],[number],'???' from [section],[subsection],[number]
GO

select [section],[subsection],[number],blah from thetable

drop table [section]
drop table [subsection]
drop table [number]
drop table thetable

quote:

---

Originally posted by byrmol

I obviously don't get it because how that models to 42 tables is beyond me.

I see 3 domains...Area, Constituents and EntrySequence.
Your requirement implies that all the domains (columns) are needed as the key in "something" and that the EntrySequence is internal to the Area,Constintuent value.

Magic 8 ball says.... 1 table

DavidM

A front-end is something that tries to violate a back-end.

---

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myStage99(RowNum int IDENTITY(1,1), col1 varchar(8000))
GO

INSERT INTO myStage99(Col1)
SELECT 'one/a/001, one/a/002, one/a/500' UNION ALL
SELECT 'one/b/001, one/b/002, one/b/500' UNION ALL
SELECT 'one/c/001, one/c/002, one/c/500' UNION ALL
SELECT 'one/d/001, one/d/002, one/d/500' UNION ALL
SELECT 'one/e/001, one/e/002, one/e/500' UNION ALL
SELECT 'one/f/001, one/f/002, one/f/500' UNION ALL
SELECT 'two/a/001, two/a/002, two/a/500' UNION ALL
SELECT 'two/b/001, two/b/002, two/b/500' UNION ALL
SELECT 'two/f/001, two/f/002, two/f/500' UNION ALL
SELECT 'seven/a/001, seven/a/002, seven/a/500' UNION ALL
SELECT 'seven/b/001, seven/b/002, seven/b/500' UNION ALL
SELECT 'seven/f/001, seven/f/002, seven/f/500'
GO

CREATE TABLE mySection_SubSection99([Section] varchar(20), SubSection char(1), SubSectionNum char(3))
GO

DECLARE   @RowNum int, @MAX_RowNum int, @s int, @e int, @col1 varchar(8000), @parse varchar(24)
	, @Section varchar(20), @SubSection char(1), @SubSectionNum char(3)

SELECT @RowNum = 1, @MAX_RowNum = MAX(RowNum), @s = 1, @e = 1 FROM myStage99

WHILE @RowNum <= @MAX_RowNum
  BEGIN
	SELECT @Col1 = Col1 FROM myStage99 WHERE RowNum = @RowNum
	SELECT @e = CHARINDEX(',',Col1,@s)-1 FROM myStage99 WHERE RowNum = @RowNum
	WHILE @e > -1
	  BEGIN
		SELECT @Parse = SUBSTRING(Col1, @s, @e-@s+1) FROM myStage99 WHERE RowNum = @RowNum
		
		INSERT INTO mySection_SubSection99([Section], SubSection, SubSectionNum)
		SELECT    SUBSTRING(@Parse,1,CHARINDEX('/',@Parse)-1) AS [Section]
			, SUBSTRING(@Parse,CHARINDEX('/',@Parse)+1,1) AS SubSection
			, RIGHT(@Parse,3) AS SubSectionNum

		SELECT @s = @e + 3
		SELECT @e = CHARINDEX(',',Col1,@s)-1 FROM myStage99 WHERE RowNum = @RowNum
	  END

	SELECT @Parse = REVERSE(SUBSTRING(REVERSE(Col1), 1, CHARINDEX(',',REVERSE(Col1))-2)) 
	  FROM myStage99 WHERE RowNum = @RowNum

	INSERT INTO mySection_SubSection99([Section], SubSection, SubSectionNum)
	SELECT    SUBSTRING(@Parse,1,CHARINDEX('/',@Parse)-1) AS [Section]
		, SUBSTRING(@Parse,CHARINDEX('/',@Parse)+1,1) AS SubSection
		, RIGHT(@Parse,3) AS SubSectionNum

	SELECT @RowNum = @RowNum + 1, @s = 1, @e = 1
  END
GO

CREATE TABLE [dbo].[mySection99] (
	[Section] [varchar] (20) NOT NULL PRIMARY KEY
) ON [PRIMARY]
GO

INSERT INTO mySection99([Section]) 
SELECT DISTINCT [Section] FROM mySection_SubSection99
GO

ALTER TABLE dbo.mySection_SubSection99 ADD CONSTRAINT
	FK_mySection_SubSection99_mySection99 FOREIGN KEY
	(
	[Section]
	) REFERENCES dbo.mySection99
	(
	[Section]
	)
GO

SELECT * FROM mySection99
GO

SELECT * FROM mySection_SubSection99
GO

DROP TABLE myStage99
DROP TABLE mySection_SubSection99
DROP TABLE mySection99
GO