Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table design Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 18, 2010 4:52 AM
Points: 1, 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
Post #883939
Posted Tuesday, March 16, 2010 12:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #884123
Posted Saturday, April 10, 2010 1:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #901137
Posted Wednesday, January 26, 2011 2:02 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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
Post #1054218
Posted Wednesday, January 26, 2011 2:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:38 AM
Points: 7,850, Visits: 9,600
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
Post #1054238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse