SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Database Design
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/09/2005 :  10:31:04  Show Profile  Reply with Quote
I have a database that will be storing records from 7 different sections. Each section will have 6 sub sections. The records from each separate subsection under each section will have to be stored in their own individual table as each subsection within each section has an individual numbering system.
The problem with this is that it will create 42 tables in the database but I don' want this.

Has anyone any suggestions for a better way to store this information.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 06/09/2005 :  10:43:36  Show Profile  Reply with Quote
It would help a lot if you post some sample data



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 06/09/2005 :  11:06:15  Show Profile  Reply with Quote
if sections and sub sections have the same structure, use a recursive table that has a "ParentSectionID" column. It would be null for your seven main sections that do not have parents.
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/09/2005 :  11:12:48  Show Profile  Reply with Quote
The 7 sections are one, two, three, four, five, six, seven
Under each section we have 6 subsections called a, b, c, d, e, f
Each will have a number generated automatically and attached to the end of it.

so we have:
one/a/001, one/a/002, .... one/a/500
one/b/001, one/b/002, .... one/b/500
one/c/001, one/c/002, .... one/c/500
one/d/001, one/d/002, .... one/d/500
one/e/001, one/e/002, .... one/e/500
one/f/001, one/f/002, .... one/f/500

two/a/001, two/a/002, .... two/a/500
two/b/001, two/b/002, .... two/b/500
.
.
.
two/f/001, two/f/002, .... two/f/500

.
.
.
.
.
seven/a/001, seven/a/002, .... seven/a/500
seven/b/001, seven/b/002, .... seven/b/500
.
.
.
.
seven/f/001, seven/f/002, .... seven/f/500

As you can see the numbering system is unique for each one and the records cannot be stored in one table.

Have you a better way to do this?
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/09/2005 :  11:31:00  Show Profile  Reply with Quote
Coolerbob can you supply any web links that explain recursive tables?
I know nohing about them.
macca
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/09/2005 :  11:33:36  Show Profile  Visit spirit1's Homepage  Reply with Quote
what he means is trees and hierarchies.
see this:
http://www.seventhnight.com/treeStructs.asp

it explains it well.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/09/2005 :  11:56:47  Show Profile  Reply with Quote
Which has nothing to do with that data.

I would normalize the data and have 1 table with 3 columns



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/09/2005 :  12:32:33  Show Profile  Reply with Quote
42 tables is not normalized ?

What additional information are You storing apart from the section/subsection/sequencenumber ???

Edit: is it always 1-500 ?

rockmoose

Edited by - rockmoose on 06/09/2005 12:33:46
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/09/2005 :  12:45:22  Show Profile  Reply with Quote
quote:
Originally posted by rockmoose

42 tables is not normalized ?



Not for that data it's not...you're kidding right?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/09/2005 :  13:39:39  Show Profile  Reply with Quote
A disagreement

Having 42 or 1 table(s) in which to store the data has nothing to do with normalization.
It has to do with "Principle of Orthogonal Design" (POOD), not normalization.

Ok, it might be bad design, but it's still normalized.

Here is a short explanation of concepts from C.J.Date (with the posh terms)
POOD = meanings of relvars must not overlap
normalization = meanings of rows within relvars must not overlap
I dug up this link: http://www.dbdebunk.com/page/page/622312.htm for those interested.

I think I am right, but would be happy to be proven wrong.

rockmoose
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/10/2005 :  04:16:20  Show Profile  Reply with Quote
X002548 and rockmoose could either of you give me an example of what you are talking about using some of the data I have posted above.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/10/2005 :  13:35:03  Show Profile  Reply with Quote
>> X002548 and rockmoose could either of you give me an example of what you are talking about using some of the data I have posted above.
We just said that 42 tables is probably worse design than having 1 table.
I argued that You can have 42 tables and still be normalized.

But please,
Give us some more information on what You are doing.
What is the meaning of 1-500 ?. Does the number have any meaning, couldn't it be just any sequential number ?
What other information are You planning to store apart from the section/subsection/number ?

If You want "pretty numbers":
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


rockmoose
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/10/2005 :  14:01:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Sounds like two tables to me: Section and a related table, SubSection.

Why does each "subsection" need it's own table? Just because they have different "numbering" is no reason.

It might help to forget about tables, SQL, databases and normalization and simply explain in a brief paragraph the business requirements you need to implement. We generally cannot help you effectively and you yourself cannot solve your own problem until the business requirements are clearly stated.

- Jeff
Go to Top of Page

Antonio
Posting Yak Master

United Kingdom
168 Posts

Posted - 06/10/2005 :  15:39:52  Show Profile  Visit Antonio's Homepage  Reply with Quote
Does the table width and/or datatypes change for different subsections? More concise definition of your problem would be a great help.
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/13/2005 :  04:52:19  Show Profile  Reply with Quote
Thanks for the replies guys.
Here are the actual requirements I have:

I have 7 areas namely, Europe, Canada, America, Africa, Australia, Asia, Brazil. Each of these are assigned letters to denote them as follows:
Europe - E, Canada - C, America - A, Africa - Af, Australia - Au, Asia - As, Brazil - Br.

Within each of the above areas we have the following 6 constituents which are common to all the above areas:
County, Province, City, Town, Village, Street.
Each of the constituents is denoted by letters also:
County - C, Province - P, City -Ci, Town -T, Village - V, Street - S.

I can create a record and select any area, if i select Europe I will be able to select any of the 6 constituents. When a record is created we want it to generate a record number, so if I select Europe and then select County the record number will be EC001, E for Europe and C for County and 001 as this will be the first EC record created.
If I create another order and it is America and Town the number for it will be AT001, A for America and T for Town and 001 because it is the first AT record raised.
This numbering system is the same for all 7 areas with their 6 constituents. And Each one will start at 001 so that will be 7 times 6 equals 42 different numbering systems. The numbers starting from 001 will be automatically incremented everytime one of the 42 records are created going from 001, 002, 003 .............

I could do this by using 42 separte tables for the 42 diferent numbering systems but would like a better way to do it.

So anyone any ideas on this?
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/13/2005 :  06:12:26  Show Profile  Reply with Quote
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.
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/13/2005 :  06:27:27  Show Profile  Reply with Quote
Thanks for the reply byrmol.

How would you put all this in one table and be able to generate a new number when each record is created. When you have the 42 different types how can you incremet the new record by 1 and making sure it is correponding to the correct one of the 42 different types.

If I create an EC it will be EC001, and then create an AP it will be AP001, and then create an AUT it will be AUT001. But if I then create another EC it will be EC002 and then another EC it will be EC003. But if I then create another AUT it will be AUT002 and if I then create another AP it will be AP002.
I need some way to keep control of each of the numbering systems as each of the 42 different types is incremented separately from one another.

How can I put all this into one table?

macca
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/13/2005 :  07:25:57  Show Profile  Reply with Quote
Don't fixate on having identity columns.
Draw the next number(s) for the Area+Constituent from the "EntrySequence" domain.

Just a side note, I don't really understand why it is important that the numbers in the sequence must be:
1,2,3,4,5,6...
If they are unique and ascending, then You logically have ordering.
1,45,87,88,90,101... (can always be "prettified" to 1,2,3,4,5,6... if You want to).

The example in my previous post was a simplified example of the: Area, Constituents, EntrySequence ==> 1 Table.

rockmoose
Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 06/13/2005 :  07:33:44  Show Profile  Reply with Quote
The numbers need to be in the sequence 1,2,3,4,5 ..... because they are record 1, record 2 , record 3, record 4, record 5........

What you mean by "Entry Sequence Domain"
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/13/2005 :  08:33:47  Show Profile  Reply with Quote
A range of valid numbers that are assigned to the entered records.
This is not necessary, but can be practical depending on how You implement the solution.

If You go with the 1 table solution;
The way I see it is that You have basically 2 options for the number sequence.
a) Maintain the sequence programatically Yourself so You get it: 1,2,3,4,5...
b) Use an Identity and let sql server generate the sequence. This will mean that for any specific Area+Constituent,
the sequence can look like 3,56,78,122,213...

a) & b) are logically equivalent, and contain the same information.

How are You going top query the database ?
Is it like this:
Give me the 22nd E C entry ?, You CAN do that in both case a) & b).

Are You really using the order of entry as an identifier ?
Why not entry date ?
Or the name of the County/Province/City/Town/Village/Street ?
So many questions....

------------------------------------------------------------------------------
Could You provide sample structure + data for the database in it's current state or as You envision it.
It would help us a lot in figuring out Your requirements.





rockmoose

Edited by - rockmoose on 06/13/2005 08:36:14
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/13/2005 :  10:18:04  Show Profile  Reply with Quote
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.



OK, so it's really gonna be 2 tables....could be 1...but for the full logical model it's 2. The Hard part is the trick in normalizing the data...like:


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



Good Luck



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000