Year 12s: what will be the first way you research your future options? >>

Question anout ERD relationsships..

this is regarding A2 ICT...

is it possible to have 2 relations for 1 field... I.e. tblStock has the primary key of StockID... when i normalised it ended up as StockID will have a relation with SupplierID an it will also link to the StockID in my purchase tbl... is this possible or should i try to link using another field?

Reply 1

theArchitect

You mean StockID is the foreign key in both the supplier and purchase tables? Yes, that's fine.

Reply 2

TSR Group Staff

no problems there, and pretty common with the database i'm doing at the minute, just make sure the tables are connected appropriatly(sp?)...

... in mine (although at uni) it ended up having to have numerous links between the tables, but i never needed to do that at A2.

Reply 3

FatMax

theArchitect

You mean StockID is the foreign key in both the supplier and purchase tables? Yes, that's fine.

It's *unlikely* to be a foreign key in the suppliers table

FatMax

Reply 4

sstez

FatMax

It's *unlikely* to be a foreign key in the suppliers table

FatMax

mmm why??? how about if I put Supplier ID in the stock table???

Reply 5

FatMax

sstez

mmm why??? how about if I put Supplier ID in the stock table???

Reply 6

sstez

mm another question.. can I have 1 primary key and 2 composite key in 1 table?

my purchase table has has PurchaseID(primary) CustomerID(for link with customertbl) and stockID for link with stocktbl... is this logical??

or should i put a PurchaseID into customer table and link it that way?

thnx for the help by teh way

Reply 7

iainmacn

Supplier ID into the stock table makes more sense - one supplier will provide (potentially) lots of stock items, and while each item *might* be supplied by more than one supplier, I imagine they have one each, so it's a one-to-many. With these, the primary key from the one end always goes into the entity at the many end as the foreign key, not vice verse (i.e. I'm in agreement with FatMax).

sstez - I don't see a composite key - I see a primary key and two foreign keys unless I've misunderstood you - which is fine. It's also fine if you do have a composite key for each field within it to be a foreign key - in fact it's quite common. e.g. if Students are enrolling for subjects at a college, Student-Subject is many to many, so use Enrol as a link table. Student ID and Subject ID could be used as a composite key, and Student ID will link to the student table (so a foreign key) and Subject ID to the subject table.

Hope that makes sense!