June 9, 2005 at 8:46 am
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.
June 9, 2005 at 9:51 am
I would need more information about your problem to recommend any kind of solution.
What is the nature of your issue?
Sql server doesn't care if you create 1 or 42 tables, but it will depend on what your issue is.
June 9, 2005 at 11:12 am
The programmers will care if you have 42 tables when 3 could do the same job... what problem are you trying to solve by having 42 tables??
June 10, 2005 at 2:33 am
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?
June 10, 2005 at 3:20 am
Why can't the rows be stored in the same table? On the contrary, the fact that they have their own numbering systems is good since it lets you have them in the same table with a unique key.
June 10, 2005 at 3:28 am
Chris,
Thanks for the reply man.
I'm pretty new to database design, how would you do what you have just suggested.
macca
June 10, 2005 at 3:37 am
Chris,
The reason I don't want to store the numbers in the same table is that the numbers have to be automaticaly generated for each one i.e. from 1 to 2 to 3 etc. And in order to do this I will have to check the last number entered and then to increment it by one. But what if I want to increment one/a/001 but I also have two/a/002 then the next number generated would end up being ...003 as it would have picked up the 002 and incremented.
macca
June 10, 2005 at 4:38 pm
In this case you are using the identity property incorrectly. Identity is a SQL Server proprietary syntax for generating a new number for each row, guaranteed to be higher than any other created for a prvious row in the same table. It should not be used to generate data that has meaning.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply