Thank you for your constant help. I think I may have a good question today. I confess that I learned a lot from your book and I have to say one of the common mistakes I did until I read your book it was to use extensively artificial primary keys. Now understanding the reason why it is not such a great idea I am using natural primary keys (as composite where needed). But I have a question related to the programming methods. It is somehow obvious that there may be situations when using an artificial key will be easier and in fact there are frameworks that are doing precisely this thing.
My situation it's the following:
Code:
Users
-----
UserID PK
Language
---------
UserID
NameLanguage FK
LevelLanguage FK
Languages
----------
NameLanguage FK
It seems natural to user Language.UserID and Language.NameLanguage as primary key for this table. Now comes the tricky thing. When I had an artificial primary key I was able to modify and delete records by simply passing that key as parameter in URL.
.../modify.php?language=<?php echo $LanguageID;?>
where the variable holds the value from the artificial key "Languages.LanguageID". Simply and efficient.
Now when I have a composite key it seems that a lot of programming may be required. What one should do in this circumstances?
I believe that UserID may be taken from the $_SESSION['id'] and what we pass as parameter in the URL will be the Language.NameLanguage, hence not really a lot of work.
But I guess there will be situations when one of the values of a compound key will not be so easily available like the UserID, in which case what do we need to do? Pass both values as parameter? How about when we have a compound key with 3 or more keys? Wouldn't be easier to make an artificial key that will save us a lot of time and trouble? As far as the database will not have consistency problems, is it fair as programmer to save time by using artificial primary keys?
(No, I am not obsessed with breaking the normalization rules...

)
-------------------------------------------------------------------------------------------------------------------
PS: such difficult of implement example you were kind to discuss in a previous thread where the answer was:
Quote:
If the user could have more than one job at a single company, you would need userid + start + company + position.
|
for the table example:
Code:
experience
------------
start
end
position
company
industry
userid
Here an artificial key will make things soooo much easier...
-------------------------------------------------------------------------------------------------------------------
I think I can top the previous example:
Code:
education
------------
userid
start
end
school
industry
degree char(1) Y/N
What combination of keys will make the primary key?
There are students that are taking two degrees in the same time at the same university, and until they get their degree the degree will say "no". The userid, start, end, degree, school, even industry may be the same.
For example an user may be first year at an university looking for a degree in software (anything) and one in hardware (anything). The input will look like this:
2011-2012 | University of Pensilvania | IT | no
2011-2012 | University of Pensilvania | IT | no
When the web site is big it is normal to overlap some industries/domains like "IT". What needs to be done in this example?
----------
For completeness I should add the following: there are several standards, one of them is for European countries, called "Europass". Their fields are the following:
Education and training
Dates (beginning - end)
Title of qualification awarded
Principal subjects/occupational skills covered
Name and type of organization providing education and training
Level in national or international classification
This is a real example and on their web site, to my surprise, "Level in national or international classification" is a text field. "Name and type or organization" it's split on usual name + address fields. "Title or qualification" is a textfield. "Skills" is a textarea (surprise). Dates are dates.
I guess the primary key at first glance seems UserID+Qualification. But how about the cases when somebody is taking the same qualification in two different geographical areas (countries in Europe or states in United States) as when required to pass the qualification for that country or state. Even simpler, how about a college student who is working for two degrees at the same university and did not get any license because it's undergoing (or to be mean, because did not passed the exams).
Also I guess the level could be involved as well in discussion... What is your opinion for this, I will say, difficult situation?
Applying what you teach us in a previous thread when we were talking about the "Experience":
Quote:
If the user might have more than one job at the same time, you would need userid + start + company. That assumes a user has only one job at a time at any single company.
If the user could have more than one job at a single company, you would need userid + start + company + position.
|
I guess I will take userid + end + qualification
or
userid + end + qualification + institution
Not even here an artificial key will make sense? One small educationID and all problems are solved and at the programming level we pass this id instead 3 extra parameters.
I apologies for the long thread...