New to SQl and programming in general!

Relationship between 2 tables

Page 1 of 1

4 Replies - 567 Views - Last Post: 25 March 2009 - 06:59 AM Rate Topic: -----

#1 scotta01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 05-January 09

New to SQl and programming in general!

Post icon  Posted 25 March 2009 - 03:37 AM

Hi guys,
I am an absolute novice when it comes to databases. I have 2 tables which I have put together. One is a software list with licenses and expiry dates etc, and the other is a hardware one with Make, Model, Serial number location etc.

I am now trying to set up a relationship between the 2 but cant seem to get my head round getting more than one row associated to another. If I put a link between the primary keys I can associate one piece of software to one machine, but how would I add it to more.

I hope that makes sense, sorry for the probably very simple question.

Is This A Good Question/Topic? 0
  • +

Replies To: New to SQl and programming in general!

#2 nofear217  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 14
  • View blog
  • Posts: 323
  • Joined: 08-November 07

Re: New to SQl and programming in general!

Posted 25 March 2009 - 06:27 AM

One thing that you could do would be to create a third table that would reference the other two...basically a table that would have an identity column and then a column that references the primary key in the other two tables (these would be your FK's).

So you would have your licenses table:

LicenseNum (or some other identifying characteristic)
Name
Company
ExpirationDate

Your hardware table:

Make
Model
SerialNum

And your join table:

IdentityNum
LicenseNum (from software table)
SerialNum (from hardware table)

You could also add an identity column to the original two tables and use their identities in place of the LicenseNum and SerialNum in the Join table.

This post has been edited by nofear217: 25 March 2009 - 06:44 AM

Was This Post Helpful? 1
  • +
  • -

#3 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: New to SQl and programming in general!

Posted 25 March 2009 - 06:48 AM

Hi!

Suppose that your TableOne has - amongst others - the fields (columns) OneId and Product. Furthermore, suppose your TableTwo has - amongst others - the fields TwoId, SoftWare and IdInTableOne.

This way one Product can be associated to several SoftWare (each SoftWare-entry having a reference to one Product-entry).

This is called a one-to-many-relationship. It needs only two tables.

Then there is the many-to-many-relationship. In that case - e.g. when you want many students in a class AND many classes for each student - where you need three tables. Table one with it's OneId, Table two with it's TwoId and the third table that (basically) only consists of two columns, one holding an Id from table One and the other holding an Id from table Two. For each relation between table one and two you put an entry in table three with the identity pair from One and Two respectivly.

Hope this helps.

Regards
/Jens
Was This Post Helpful? 1
  • +
  • -

#4 nofear217  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 14
  • View blog
  • Posts: 323
  • Joined: 08-November 07

Re: New to SQl and programming in general!

Posted 25 March 2009 - 06:52 AM

^ exactly if a license can only go to one piece of hardware, otherwise you will need the many-to-many relationship that I described or that jens covered in the second portion of her topic.
Was This Post Helpful? 0
  • +
  • -

#5 scotta01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 05-January 09

Re: New to SQl and programming in general!

Posted 25 March 2009 - 06:59 AM

Thank you, I knew there would be a simple answer.

Didn't think of a separate table!

Thank you
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1