March 16, 2010 at 9:58 am
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
March 16, 2010 at 12:58 pm
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
April 10, 2010 at 1:19 pm
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.January 26, 2011 at 2:02 pm
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
January 26, 2011 at 2:27 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply