C# New to database, help

[wingers1290]

Hi, i'm trying to create a program so i can log every person I meet, which i have done quite easily with databases. However i would also like to be able to log each time i meet that same person which is where i can't figure out what to do. T would seem to me like i would need two databases, one for the personal information, name, country etc and another for each time I meet them. I Have been unable to do this and ask if anybody can offer me suggestions?

[apegram]

You don't need two databases, you need one database with two tables. One would be a Person table (or whatever you want to call it) where you store the relevant personal details (name, country, etc.) and the other would be a PersonEncounter (or whatever you want to call it) where you take the unique identifier from the Person table (PersonID?) and use that as a foreign key. You can then use this Encounter table to store when, where, etc.

The key is one database, two tables, primary/foreign keys, and keep the data as normalized as possible.

[wingers1290]

Oh I see, as i said i'm very new to this. so if I had two tables how would I display the person encounter table for that specific person. I get having two tables but not how to get the second table to display information linked to the first table.

[apegram]

OK, let's assume you have a Person table with the following fields

PersonID (auto-incremented integer, primary key)
LastName
FirstName
Country

And you have a PersonEncounter table with the following fields

PersonEncounterID (auto-incremented integer, primary key)
PersonID (foreign key, references Person.PersonID)
DateEncountered
Location

And, for the purposes of this exercise, you previously encountered a person named John Smith in Russia, and his auto-assigned ID is 17. To get the list of all encounters with John, you would use a SQL statement like this:

Select * From PersonEncounters Where PersonID = 17 -- ID for John Smith

If you wanted John's information included along with the encounter details, you would do a join, usually an inner join.

Select p.*, e.*
From Person p
Inner Join PersonEncounters e
On p.PersonID = e.PersonID
Where p.PersonID = 17

In this case, * means "all fields." The p and e (Person p, PersonEncounters e) are aliases you can use instead of the full names, it just saves time typing. You do not have to pull all fields, though. You can reference them explicitly.

Select p.FirstName, p.LastName, p.Country, e.DateEncountered, e.Location
From Person p
Inner Join PersonEncounters e
On p.PersonID = e.PersonID
Where p.PersonID = 17

[wingers1290]

Ok, thanks, this is starting to make sense. Just a quick question, how to you set the foreign key and refrence PersonID?

[apegram]

It can vary based off what platform you are using or what stage your table may be in (doesn't exist, already exists...). Consult this link for more information, as well as other information you might want regarding working with your database.

http://www.w3schools.com/Sql/sql_foreignkey.asp

[wingers1290]

Thanks for your help, i think i'll read the basic on that page and work through it. Cheers!