Table design

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Relational Theory » Table design

Table design

Rate Topic

Display Mode

Topic Options

Author

Message

IPO

Posted Tuesday, March 16, 2010 9:58 AM

Forum 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

AndrewSQLDBA

Posted Tuesday, March 16, 2010 12:58 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 796, Visits: 2,280

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

PaulB-TheOneAndOnly

Posted Saturday, April 10, 2010 1:19 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 2,988, Visits: 4,412

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

Vedran Kesegic

Posted Wednesday, January 26, 2011 2:02 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 343, Visits: 1,077

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

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1054218

L' Eomot Inversé

Posted Wednesday, January 26, 2011 2:27 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 7,112, Visits: 7,188

Vedran Kesegic (1/26/2011)

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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

Post #1054238

« Prev Topic | Next Topic »

Permissions