That is it : I am becoming a programmer.

[Diestl]

The trick with programming is to get your table structure right. If you dont have control of the table structure, and its not right, you can be in line for a world of pain.
I am working on a legacy system, two main tables
table1 primary key is a long
table2 foreign key to table1 primary key is a varchar

some days I just want to die

You should abstract the database structure away using OO layers.

[MrMark]

The trick with programming is to get your table structure right. If you dont have control of the table structure, and its not right, you can be in line for a world of pain.
I am working on a legacy system, two main tables
table1 primary key is a long
table2 foreign key to table1 primary key is a varchar

some days I just want to die

Designing the structure of a database system should be the simplest task of the whole process. How do so many people supposedly experienced in IT get it so wrong?

[MrMark]

In my interview last week I was asked "Explain how you would create a database".

When I got to "Each table typically requires a primary key which should be a unique ID that does not exist in the real world" the boss man stopped me and asked me to explain.

He could not grasp that using someone's name, for example, as a primary key won't work.

"But women change their name when they marry" says I.

His answer? "So you just change the primary key. What's the problem here?"

Maybe I should have done my presentation on fourth normal form.

This boss man is the head of a data analysis section.

It is possible to have a primary key based on a unique id that exists in the real world eg sales number or invoice number, but you're right that normally a new sequence or unique string would be initialised. I can't believe that the head of a data analysis section can come out with that guff. Did you ask him to search on John Smith's or Muhammed Patel's records?

[TimberWolf]

I once failed a trick question based on database keys. I don't think I would have been so easily tricked had I not been in an interview situation though. The question was quite involved by the key misdirection was buried in the database requirement and that was that the primary key was not the column primarily (mainly) searched on, it was some other table field, so that was the index that should have been clustered, rather than the primary key.

[threaded]

Ohh so many thoughts...

Anyway here we are.... I have now about had it all with trying to secure a job in MS Infrastructure position .... So basically I have to start studying programming ....

Programming is an art. Some plonkers keep trying to turn it into a science. They fail, and always will fail, because it is an art.

Yes, you can have jumped up administrator types, scribbling scripts, sticking piles of COTS packages together, but they're more like Graphic Designer as compared to the programmer Fine Art artist.

[RichardCranium]

It is possible to have a primary key based on a unique id that exists in the real world eg sales number or invoice number,

Naughty, naughty. 1st rule of programming: do NOT make assumptions.

Why should a sales number be unique? Just because system A generates a number you cannot assume it is and will always be a unique number. What happens when they do a database roll-back after a disaster? What happens when you discover it rolls round at some big number? What happens when some twat in Accounts changes the cost code hierarchy and says sales numbers must be compatible with their system? What happens when the France office opens and their sales numbers start at F0001?

Will the invoice number still be unique when ClientCo merges with OtherCo?

Contrary to rule number 1: a reasonably safe assumption is that you should NEVER use real world data as a primary key.

[EternalOptimist]

Naughty, naughty. 1st rule of programming: do NOT make assumptions.

Why should a sales number be unique? Just because system A generates a number you cannot assume it is and will always be a unique number. What happens when they do a database roll-back after a disaster? What happens when you discover it rolls round at some big number? What happens when some twat in Accounts changes the cost code hierarchy and says sales numbers must be compatible with their system? What happens when the France office opens and their sales numbers start at F0001?

Will the invoice number still be unique when ClientCo merges with OtherCo?

Contrary to rule number 1: a reasonably safe assumption is that you should NEVER use real world data as a primary key.

<cough>
and Contrary to that - listen to THIS.

I am upgrading the front ends of a sytem, and migrating their data to a common database. The primary keys are non real world, and specific to an application.
EXCEPT that the databases are annualised. So 1990 has a primary key range 1-200k
1991
1 - 210k
1992
1- 198k

etc


If they had used real world data I would have been ok

[RichardCranium]

Programming is an art. Some plonkers keep trying to turn it into a science.

I agree. May I expand on that?

In the world of fine art, there are people who apply the pigments to different surfaces and leave them for many years in sunny and dark places just to see what happens to the colours over time. (At least one artist was famous for doing that with hundreds of samples.) Ditto for materials (canvas v wood etc.) and brush types and so on. These are the 'scientists' doing research to see how artists' tools can be improved or added to.

There were the computer scientists that worked out better sort algorithms than I as an applications programmer could never fully comprehend, but made use of.

The art world goes through an occasional major renaissance and many minor ones. These are the artists experimenting with their philosophy and seeing how the boundaries of art can be pushed. Art deco for the masses, cubism for the purists and so on.

Computer scientists cogitate over linguistics and modify or create new computer languages and paradigms. Relational databases, structured programming and OO came out of that. As an applications programmer I took on board some of those developments and new languages and used them as required. I did not have the time to invent my own. The mentalist programming languages such as those based on white space are there, like cubism, as they needed to be done as thought experiments to push back the boundaries of the possible.

I believe computer scientists have a vital role: to come up with new ways we can use this magnificent "spanner for the brain" (I made that expression up 30 years ago) in new and better ways. To allow us the panoply of the ugly and the elegant so we can see what is efficient and what is beautiful.

But it takes the artist to know how to use the palette and tools in an aesthetically pleasing way that will appeal to the buying customer: to combine the efficient and the beautiful. To cut code that works in the required way, quickly and in a maintainable manner.

[RichardCranium]

EXCEPT that the databases are annualised. So 1990 has a primary key range 1-200k
1991
1 - 210k
1992
1- 198k

etc


If they had used real world data I would have been ok

I disagree. The year is real world data. That is why it is broken.

[EternalOptimist]

I disagree. The year is real world data. That is why it is broken.

I said I would have been ok (my task would have been easier)

not IT would have been ok

anyways, its a right mess