Do I need a Primary key ?

[ratchet]

Just I wondering if my table requires a Primary key, when the table already has a foreign key ? Here's an Items table which contains a primary key. Now I want to link this table to 2 separate tables..

Items:ItemsID(PK),Title(FK)
Movies:Title(FK), Category, Type(DVD,VHS,Bluray)
Games:Title(FK),Category,Platform(PS3,Xbox,Nintendo)

Just to note every ItemID it is mandatory starting with "SH" hence why I taken this route. Also I have a stock table with the Title as PK - Stock:Title(PK),Quantity.

So yea, just wondering if the Movies, and Games table would be fine with out a PK ?

 

[gemma-the-husky]

yes, you would need a PK. All tables in Access need a PK to be used properly.

However you only need one table for this, as it stands


tblTitle: ID, Description, Category, DiscType(DVD,VHS,Bluray,PS3,Xbox,WII etc)

note, the use of disctype rather that just type as the field name. type is a reserved word, and best avoided as a field name

Category, could be movie, game, CD, or any other type as appropriate - so really needs to be a lookup to a category table.

DiscType could encompass the movie types and/or game types - or you could use 2 separate fields - depends which works better. - again, ideally working as a lookup.

in fact, Category could be a table related to the DiscType - since PS3, Xbox, WII etc are all games, and VHS, DVd, BluRay are all movies, in which case it would be better not to have it as a field in the Titles table

Having category in the Titles table is probably denormailising, although not a critical error

you just have a query that selects the type/category you currently want to use (or all, if you want to see everything)


so you end up with this

Titles: ID (PK), Description, DiscType (FK1)

DiscType: TypeID (PK1), TypeDescription, Category(FK2)

Category: CategoryID(PK2), CategoryDescription

so FK1 corresponds to PK1, and FK2 corresponds to PK2

hope this makes sense

 

[ButtonMoon]

ratchet,

What would be the point of permitting duplicate rows? In reality entities are distinguishable things, which in the database are identified by the attributes we record about them. If you permit duplicate rows then you are not modelling reality accurately - your model is incorrect. So the answer is yes, your tables should have keys.

Three characteristics that are useful to think about when choosing and designing keys are: Familiarity, Simplicity, Stability. A key should also identify accurately the things you intend it to identify outside the database. I don't know your data or your scenario but Title seems very unlikely to be a sensible choice of key for stock control of movies and games. Different movies commonly share the same title. I suggest you consider using a UPC, catalog code, SKU or similar attrbutes instead.

 

[ratchet]

So I re-configured my tables and set out as follows, and linked them accordingly in their relationships.

Customer:CustomerID(PK)Suburb(FK).. details
SuburbuburbID(PK),Suburb,Code
Item:ItemID(PK),Title,PlatformID(FK),Quantity
PlatformlatformID(PK),Platform,GenreID(FK)
Genre:GenreID(PK),Genre
Rental:HireID(PK),CustomerID(FK),ItemID(FK),Charge ID(FK)
Chare:ChargeId(PK),ChargeType,Charge
Checkout:HireID(PK),rentedout,Returned.

So yea when I went to setup a data entry form for the Item. I had -ItemID,Title,platform, and Genre. I set it all up but than when I test it, it won't allow me to add any text into the Title box. I get the message Join key of table not in recored set. Can anyone help me where I went wrong, I'm assuming I've linked my tables wrong or something ??