SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table design


Table design

Author
Message
IPO
IPO
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
Hi,

I was wondering if a Db design guru could provide guidance for a db newb??

I have to design a database (and front-end app) that contain ID reference numbers that relate to groups of additional numbers.

An example

ID No. 1124 (ref A) contains 30 numbers (ref B) 01, 02, 03 ect up to 30
ID No. 1101 - contains 60 numbers 01, 02, 03 up to 60 ect
ID No. 1411 - contains 70 numbers 01, 02 up to 70
ID No. 1024 - contains 20 numbers
ID No. 1045 - contains 50 numbers
ID No. 1058 - contains 40 numbers
ID No. 5214 - contains 120 numbers
ID No. 7845 - contains 160 numbers

and so on


The Ref B groups of numbers can vary in size and need to be raised and lowered as and when needed.

I'm assuming that each second set of numbers (ref B) need to have individual unique identifiers , but how would I go about creating and mapping to the first group of (ref A) numbers??

Any pointers appreciated.



Thanks,
Ipo
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4390 Visits: 3427
That is actually pretty simple. Think about storing People and numerous phone numbers for a single person. So that tells you that you will need at least three tables. The one in the middle is called an "associative" table

I am assuming that you cannot have duplicate numbers in either table. So I am going to use a PRI Key in each table. So you will end up having a one-to-many relationship. And you will be able to add or remove as many numbers in the child table as you like

ONe Table

Table1

RowID int PRI KEY
NumericID int





Many Table

Table2

RowID int PRI KEY
NumericID int




Associative Table


Table1RowID int
Table2RowID int




You can build on it form there. you can add a column to indicate whether to show the row or not. etc....

The associative table will contain the PRI Keys from both tables and those will become a composite key. Creating the constraint that will not allow a duplicate row.

Andrew SQLDBA
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13989 Visits: 4639
Why not natural keys?

RefAtbl <===>> RefBtbl

Columns in RefAtbl... ID; Key = ID

Columns in RefBtbl... ID,OtherNumber; key = (ID+OtherNumber)

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1928 Visits: 1266
Like Andrew said, it's a classic many-to-many relation. In logical data model you have two entities: A and B. One A can have (contains, is associated with, etc) multiple B's, and B can have (contains, is associated with, etc) multiple A's. When converting logical data model to phisical data model, entity A becomes a table with PK, entity B becomes a table with PK, relation "many-to-many" between them also becomes a table with two foreign keys; one referencing table A and one referencing table B.
This is a perfect example: many to many relation between Product and Order

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25811 Visits: 12494
Vedran Kesegic (1/26/2011)
Like Andrew said, it's a classic many-to-many relation. In logical data model you have two entities: A and B. One A can have (contains, is associated with, etc) multiple B's, and B can have (contains, is associated with, etc) multiple A's. When converting logical data model to phisical data model, entity A becomes a table with PK, entity B becomes a table with PK, relation "many-to-many" between them also becomes a table with two foreign keys; one referencing table A and one referencing table B.
This is a perfect example: many to many relation between Product and Order

It really depends on what the Bs are. If all you know about each B is an identifier for the B and which As it is associated with, there's no need to model the Bs as entities in there own right, so you can do what Pablo suggested and just model the As plus a derived set of entities (AB associations). In that case you end up with two tables not three.

Of course the same might be true of the As: in your database you know nothing about them except an identifier and which Bs they are associated with; in that case they two could be omitted from the model, and you would have only the AB relationship table.

However, the general case for something like this is what Andrew said: you do indeed end up modelling two base entities plus a relationship between them, which leads to three tables.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search