Forum Home
Press F1
 
Thread ID: 77377 2007-03-07 10:25:00 Database basics eldarcolonel (7392) Press F1
Post ID Timestamp Content User
531054 2007-03-07 10:25:00 This is my second database/sql related thread in a week, my apologies. I say database basics because I'm sure this is just that, but I can't remember what I'm doing. Or maybe it's the late nights. Anyway:

I'm designing a cocktail database. Three basic tables to begin with, cocktail, drink, sprit. Cocktail contains name etc and a drinkID. Drinks contains DrinkID and SpiritID. Spirit contains SpiritID and spiritName.

My plan was to create a composite primary key of spiritID and drinkID in the drink table. spiritID will then be foreign to spirits, and drinkID foreign to cocktail (forgive the backwards foreign key explanation, but you know what I mean).

That's where my memory fails, in sql server 7.0 a composite primary key can't be a foreign key, at least not the way I would like. Was access different? I'm trying this method to avoid having six spirit fields labelled spiritOne, spiritTwo etc which link directly to a spirit table, half of these fields would not often be used.

Any ideas from anyone who has designed a similar database would be much appreciated. Once again sorry for creating another thread if this question seems silly, but this is the f1 Forum :thumbs: 		eldarcolonel (7392)
531055 2007-03-07 19:27:00 You should never include a foreign key as part of your primary key. You should have a separate primary key which is automatically assigned for all tables unless there is a unique key which makes a lot of sense to use (e.g. a part number). Even then it can be beneficial to use an arbitrary, automatic key in your database to avoid problems if a part should have its number reassigned (changing primary keys should be avoided at all costs).

(To translate my example to your database, look at en.wikibooks.org I will use PostgreSQL notation.)


CREATE TABLE Cocktail (
Id SERIAL PRIMARY KEY,
CName CHAR(100) NOT NULL
);

CREATE TABLE Spirit (
Id SERIAL PRIMARY KEY,
SName CHAR(100) NOT NULL,
Description TEXT
);

CREATE TABLE CocktailSpirit (
Id SERIAL PRIMARY KEY,
CocktailId INT NOT NULL REFERENCES Cocktail,
SpiritId INT NOT NULL REFERENCES Spirit,
Quantity REAL DEFAULT 1
);

CREATE INDEX CocktailSpirit_Cocktail ON CocktailSpirit(CocktailId);
CREATE INDEX CocktailSpirit_Spirit ON CocktailSpirit(SpiritId);

All identifiers here are assigned automatically by the database (PostgreSQL provides the SERIAL keyword for this. MSSQL seems to use IDENTITY with a couple of numbers in brackets. I don't know what the numbers do, you may need to look this up). We create separate non-primary key indices on each of the identifiers to facilitate fast lookup. In practice if you only go one way through the link table (e.g. finding what spirits a cocktail contains and not which cocktail contains a given spirit) then you may get better performance from only having one extra index.

P.S. I have avoided using Drinks as a table name. I don't see how the link between a cocktail and a spirit could be described as a drink in real life so I've used an artificial name. 		TGoddard (7263)
531056 2007-03-07 21:02:00 That's what I was looking for in my dreams all last night. Thank you very much. eldarcolonel (7392)
1