Dan you there?

[quiero mas]

Sorry Dan I havent been able to work this out
am i thinking along the right lines when
1 table has a list of all the lanuages that are in the database are represented
table two has my hospitals and their details
then in the third database how do i make a relationship

table one has languages and an id next to it -- id 1 language (field name)
english id 2 language german etc down to the nth language

table two has an Id number for each hospital
id 1 Hospital (field name) Central Hospital
2 south hospital
nth nth hospital

in the last table how do i link them?
i have read about foriegn keys
Do i include the hospital information in the second table ? i.e telephone
number etc

Sorry Dan for all the questions as you know i am a beginner
Still gotta buy you tat beer - dont let me forget

[MikerRoo]

I ain't Dan but I figure to save you some beer money (I only drink snapps).

Anyway create the third table with the name LanguagesByHospital (this is a
relation table).

It needs a column called Hospital_id and another column called Language_id.

It is a good idea to also include a third column -- to avoid problems with
maintanence or expansion.
Call it iID and make it an autonumber (if using access) or an identity column
(if using SQL server).

Also, when using this kind of table it is a good idea to create an index that
requires the combination of Hospital_id and Language_id to be unique.
This will save headaches down the road and will also speed up searches.

Also you would add any data that is relationship specific, like the name of
the speaker, to this table. (Expand the aforemention index if you do.)


To create a language entry for a hospital, you would use SQL like this:

INSERT INTO LanguagesByHospital
(Hospital_id, Language_id, SpeakerName)
SELECT
(Select Hospital_id FROM HospitalTable WHERE HospitalName = 'North Nagasaki
Neonatal Nursery'),
(Select Language_id FROM LanguageTable WHERE LanguageName = 'Swahili'),
'Dale O''Malley'


To find out which hospitals had Esperanto available, you would use something
like this:

SELECT
Hosp.hospitalName,
LbyH.SpeakerName
FROM
HospitalTable Hosp
INNER JOIN
LanguagesByHospital LbyH ON LbyH.Hospital_id = Hosp.Hospital_id
INNER JOIN
LanguageTable LangTab ON LangTab.Language_id = LbyH.Language_id
WHERE
LangTab.LanguageName = 'Esperanto'


You can find an introductory article, on why to go through this hassle, at:
[url]http://www.sqlservercentral.com/columnists/bkelley/normalization.asp[/url] .
That site also has a forum you can use for SQL help.

Regards,
-- Not Dan

[quiero mas]

Not dan thanks very much - looks like the snapps are on me - or in my case sake -

thanks again

[Dan Bracuk]

I disagree. That paricular 3rd column serves no useful purpose and creates
extra work. Other columns may be useful in tables with many to many
relationships, but not that one.

Originally posted by: MikerRoo
It is a good idea to also include a third column -- to avoid problems with
maintanence or expansion.
Call it iID and make it an autonumber (if using access) or an identity column
(if using SQL server).

[MikerRoo]

I disagree.
That iID (could be a datestamp in SQL server) takes no extra work. You
create it once and then forget about it until it saves your bacon.
It uses minimal resources and has almost no noticeable impact on performance.

Its function is as a poor man's (or universal) datestamp. In can be
invaluable for replication or certain self join queries.
And, for newbies who may not set up a proper primary key (which in this case
is a two, or more, column index), it is a critical fallback.

[quiero mas]

I appreciate the discussion dan and mike - i guess i have to make a choice - As
mike points out im a newbee so hard to make an informed choice. Suddenly got
confusing -

Ill do some research and try and make an informed choice.

Thanks to both of you for taking your time to answer

Regards Mark