Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 If fields aren't columns, what's the difference?

Author  Topic 

Aleph_0
Yak Posting Veteran

79 Posts
Posted - 2011-07-19 : 18:33:51
I keep reading here that fields aren't columns and records aren't rows, but I don't know what the real difference is. Can someone explain? (Note to the angry guy: yes, I'm a beginner, but that's not an invitation for insults and condescension. I'm still human, after all, and I'm trying to learn.)

If it's not too much to ask, could you also show me when a field IS a column (if ever), and when a record is/is not a row?

As always, I appreciate your help!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-07-19 : 18:42:31
It's purely semantics. In SQL a row and a record refer to exactly the same thing and means exactly the same.

Joe's just a pedantic standards-nut that likes to insult people new to databases.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts
Posted - 2011-07-19 : 20:15:10
After setting SQL Standards and teaching SQL for 20+ years, I consider this to be the place to start. BECAUSE semantics matter and cowboy coding is a bad way to do programming.

A deck of punch cards or a mag tape is nothing like an SQL Schema. Like most new ideas, the hard part of understanding what the relational model is comes in un-learning what you know about file systems. As Artemus Ward (William Graham Sumner, 1840-1910) put it, "It ain't so much the things we don't know that get us into trouble. It's the things we know that just ain't so." Dijkstra also said the same thing about programming.

If you already have a background in data processing with traditional file systems, the first things to un-learn are:

(0) Databases are not file sets. Files do not have relationships among themselves; everything is done in applications. SQL does not mention anything about the physical storage in the Standard, but files are based on physically contiguous storage. This started with punch cards, was mimicked in magnetic tapes, and then on early disk drives.

(1) Tables are not files; they are parts of a schema. The schema is the unit of work. I cannot have tables with the same name in the same schema. A file system assigns a name to a file when it is mounted on a physical drive; a table has a name in the database. A file has a physical existence, but a table can be virtual (VIEW, CTE, query result, etc).

(2) Rows are not records. Records get names and meaning from the application reading them. Records are sequential, so "first", "last", "next" and "prior" make sense; rows have no physical ordering (ORDER BY is a clause in a CURSOR; they convert tables to sequential files. Records have a physical locator, such as pointers and record numbers. Rows have keys, which are based on uniqueness of a subset of attributes in a data model without any phsyical references. The mechanism is not specified and it varies quite bit from SQL to SQL.

(3) Columns are not fields. Fields get meaning from the application reading them -- and may have several meanings depending on the apps. Fields are sequential within a record and do not have data types, constraints or defaults. This is active versus passive data! Columns are also NULL-able, a concept that does not exist in fields. Fields have to have physical existence, but columns can be computed or virtual. If you want to have a computed column value, you do in the application, not the file.

The final problem is that ANSI/ISO Standard SQL "field" means a part of a temporal value (year, month, day, hour, minute, second).

Another conceptual difference is that a file is usually data that deals with a whole business process. A file has to have enough data in itself to support applications for that business process. Files tend to be "mixed" data which can be described by the name of the business process, such as "The Payroll file" or something like that. Tables can be either entities or relationships within a business process. This means that the data which was held in one file is often put into several tables. Tables tend to be "pure" data which can be described by single words. The payroll would now have separate tables for timecards, employees, projects and so forth.

Tables as Entities

An entity is physical or conceptual "thing" which has meaning be itself. A person, a sale or a product would be an example. In a relational database, an entity is defined by its attributes, which are shown as values in columns in rows in a table.

To remind users that tables are sets of entities, I like to use collective or plural nouns that describe the function of the entities within the system for the names of tables. Thus "Employee" is a bad name because it is singular; "Employees" is a better name because it is plural; "Personnel" is best because it is collective and does not summon up a mental picture of individual persons.

If you have tables with exactly the same structure, then they are sets of the same kind of elements. But you should have only one set for each kind of data element! Files, on the other hand, were PHYSICALLY separate units of storage which could be alike -- each tape or disk file represents a step in the PROCEDURE, such as moving from raw data, to edited data, and finally to archived data. In SQL, this should be a status flag in a table.

Tables as Relationships

A relationship is shown in a table by columns which reference one or more entity tables.

Without the entities, the relationship has no meaning, but the relationship can have attributes of its own. For example, a show business contract might have an agent, an employer and a talent. The method of payment is an attribute of the contract itself, and not of any of the three parties. This means that a column can have a REFERENCES to other tables. Files and fields do not do that.

Rows versus Records

Rows are not records. A record is defined in the application program which reads it; a row is defined in the database schema and not by a program at all. The name of the field in the READ or INPUT statements of the application; a row is named in the database schema. Likewise, the PHYSICAL order of the field names in the READ statement is vital (READ a,b,c is not the same as READ c, a, b; but SELECT a,b,c is the same data as SELECT c, a, b.

All empty files look alike; they are a directory entry in the operating system with a name and a length of zero bytes of storage. Empty tables still have columns, constraints, security privileges and other structures, even tho they have no rows.

This is in keeping with the set theoretical model, in which the empty set is a perfectly good set. The difference between SQL's set model and standard mathematical set theory is that set theory has only one empty set, but in SQL each table has a different structure, so they cannot be used in places where non-empty versions of themselves could not be used.

Another characteristic of rows in a table is that they are all alike in structure and they are all the "same kind of thing" in the model. In a file system, records can vary in size, data types and structure by having flags in the data stream that tell the program reading the data how to interpret it. The most common examples are Pascal's variant record, C's struct syntax and Cobol's OCCURS clause.

The OCCURS keyword in Cobol and the Variant records in Pascal have a number which tells the program how many time a sub-record structure is to be repeated in the current record.

Unions in 'C' are not variant records, but variant mappings for the same physical memory. For example:

union x {int ival; char j[4];} myStuff;

defines myStuff to be either an integer (which are 4 bytes on most modern C compilers, but this code is non-portable) or an array of 4 bytes, depending on whether you say myStuff.ival or myStuff.j[0];

But even more than that, files often contained records which were summaries of subsets of the other records -- so called control break reports. There is no requirement that the records in a file be related in any way -- they are literally a stream of binary data whose meaning is assigned by the program reading them.

Columns versus Fields

A field within a record is defined by the application program that reads it. A column in a row in a table is defined by the database schema. The datatypes in a column are always scalar.

The order of the application program variables in the READ or INPUT statements is important because the values are read into the program variables in that order. In SQL, columns are referenced only by their names. Yes, there are shorthands like the SELECT * clause and INSERT INTO <table name> statements which expand into a list of column names in the physical order in which the column names appear within their table declaration, but these are shorthands which resolve to named lists.

The use of NULLs in SQL is also unique to the language. Fields do not support a missing data marker as part of the field, record or file itself. Nor do fields have constraints which can be added to them in the record, like the DEFAULT and CHECK() clauses in SQL.

Relationships among tables within a database

Files are pretty passive creatures and will take whatever an application program throws at them without much objection. Files are also independent of each other simply because they are connected to one application program at a time and therefore have no idea what other files looks like.

A database actively seeks to maintain the correctness of all its data. The methods used are triggers, constraints and declarative referential integrity.

Declarative referential integrity (DRI) says, in effect, that data in one table has a

particular relationship with data in a second (possibly the same) table. It is also possible to have the database change itself via referential actions associated with the DRI.

For example, a business rule might be that we do not sell products which are not in inventory.

This rule would be enforce by a REFERENCES clause on the Orders table which references the Inventory table and a referential action of ON DELETE CASCADE Triggers are a more general way of doing much the same thing as DRI. A trigger is a block of procedural code which is executed before, after or instead of an INSERT INTO or UPDATE statement. You can do anything with a trigger that you can do with DRI and more.

However, there are problems with TRIGGERs. While there is a standard syntax for them in the SQL-92 standard, most vendors have not implemented it. What they have is very proprietary syntax instead. Secondly, a trigger cannot pass information to the optimizer like DRI. In the example in this section, I know that for every product number in the Orders table, I have that same product number in the Inventory table. The optimizer can use that information in setting up EXISTS() predicates and JOINs in the queries. There is no reasonable way to parse procedural trigger code to determine this relationship.

The CREATE ASSERTION statement in SQL-92 will allow the database to enforce conditions on the entire database as a whole. An ASSERTION is not like a CHECK() clause, but the difference is subtle. A CHECK() clause is executed when there are rows in the table to which it is attached.

If the table is empty then all CHECK() clauses are effectively TRUE. Thus, if we wanted to be sure that the Inventory table is never empty, and we wrote:

CREATE TABLE Inventory
( ...
CONSTRAINT inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0), ... );

it would not work. However, we could write:

CREATE ASSERTION Inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0);

and we would get the desired results. The assertion is checked at the schema level and not at the table level.



--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

Aleph_0
Yak Posting Veteran

79 Posts
Posted - 2011-07-19 : 20:22:16
quote:
Originally posted by GilaMonster

It's purely semantics. In SQL a row and a record refer to exactly the same thing and means exactly the same.


And a field and a column also refer to exactly the same thing? Why do the standards make a distinction at all?

Edit: My first time being sniped! Too bad I don't remember how to insert the graphic!
Go to Top of Page

Aleph_0
Yak Posting Veteran

79 Posts
Posted - 2011-07-19 : 20:42:18
Thanks jceiko - I didn't understand any of the programming references (I'm not a programmer) but you did confirm my suspicions that SQL was based on a set theoretical model (I'm a mathematician, if the handle didn't give it away!). It'll take some time to digest what you wrote - you completely lost me towards the end. Thanks again!
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-07-19 : 22:23:13
Aleph, do not be fooled by Joe's self-promoting bs. He barely understands the subject himself in the real world. He's been teaching SQL for 20 years? Scary. I bet SQL USA was his best student!

The terms "row" and "record" are interchangable.
The terms "field" and "column" are as well.

As Gail said, it's semantics. Give me a few minutes to read the entire hunk of garbage he posted and I'll bet I find a lot more horse crap in there.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-07-19 : 22:45:46
I got tired trying to read the whole thing, but here are a few remarks:

A "Standard" is a set of recommendations, built collaboratively by various companies and scholars. As such, it is NOT a set of rules. In the real world standards are almost never followed. Each vendor builds in their own proprietary syntax, functions and rules. This is a good thing. And this is exactly what happens in a free market economy.

In SQL Server, there is a clear distinction between tables, views, CTEs, query results. None of these are tables except for tables.

Your distinction between rows and records is not quite correct. In real life, the terms are used interchangably. Of course we can create a "record" which is not a physical row, but how do you know that your physical row is a physical row? Guess what? You DON'T. There might be a pointer to a physical address of the information you're trying to retrieve in your physical row. To the SQL Programmer, the distinction is non-existant.

Your entire paragraph about fields vs columns is flawed. Neither can exist in SQL Server without a physical existence. Even a computed column. And the field/column should have meaning the moment it's declared. Also, in SQL Server, the computed column IS saved in the file. At least the formula for it is. And if you persist it...

"a file is usually data that deals with a whole business process" -- Say what? You don't actually believe that do you? Does a SQL Server log (ldf) file contain an entire business process? Does a flat file containing addresses contain an entire business process? Does a Key file that contains an encryption key only contain an entire business process?

"The payroll would now have separate tables for timecards, employees, projects and so forth" We agree on a point wow! I bet you read that somewhere didn't you?

"Thus "Employee" is a bad name because it is singular; "Employees" is a better name because it is plural" -- your opinion. I prefer singular, but it makes no real difference. I like folks to be consistent though.

"The use of NULLs in SQL is also unique to the language" -- rubbish. I can name a whole bunch of languages that support the idea of a NULL. In fact, I'm struggling to think of one that doesn't, though I'm sure they exist.

"Nor do fields have constraints which can be added to them in the record, like the DEFAULT and CHECK() clauses in SQL" Nonsense. Of course they can. In ANY mathematical, sceintific, or programming model, one is free to make certain rules to make the data meaningful.
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts
Posted - 2011-07-21 : 14:04:05
>> .. (I'm not a programmer) but you did confirm my suspicions that SQL was based on a set theoretical model (I'm a mathematician, if the handle didn't give it away!). It'll take some time to digest what you wrote - you completely lost me towards the end. Thanks again! <<

Math was my first Masters and I might get back to teaching it again at the local Junior College for my retirement job. Yep, the Relational Model is set theory and relations; SQL is finite multi-sets and some computer stuff. Sorta like FORTRAN is based on Algebra and contiguous physical storage.

The SQL Standards and implementations are MUCH more abstract than the earlier programming languages.




--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts
Posted - 2011-07-21 : 16:35:31
>> A "Standard" is a set of recommendations, built collaboratively by various companies and scholars. As such, it is NOT a set of rules. In the real world standards are almost never followed. Each vendor builds in their own proprietary syntax, functions and rules. This is a good thing. And this is exactly what happens in a free market economy. <<

Sorry, Standards are often required by law and carefully defined by experts. Is a metre a matter of opinion in your world? In the case of SQL, the vendors were the guys pushing for the ANSI X3H2 committee to lock down SQL so it would not go the way of BASIC.

Do you remember when vendors did have their own programming languages? Auto-coder? Dibol? RPG? It sucked. You might want to read a history of programming languages and the Metric system. Before ISO and the modern world, every local governmental unit invented their own changeable system of weights and measures, without any precision to them and made modern capitalism almost impossible.

We have “Cowboy coders” who deliberately try to write lots of dialect to secure their jobs. It sucks and that is why large companies have coding standards. It is why professionals write maintainable code and Cowboys don't.

>> In SQL Server, there is a clear distinction between tables, views, CTEs, query results. None of these are tables except for tables. <<

Wrong. I can use a properly written virtual tables anywhere I can use a base table. They have the same data types and constraints on their columns, etc. That is why I can use them together.

When we had files with various formats such as HDAM, PHDAM, HISAM, ISAM, HIDAM, PHIDAM in just the IBM world, we had use different functions to get data out of them. I also had to do explicit type conversions and a lot of other things. These things REALLY were distinct!

>> Your distinction between rows and records is not quite correct. In real life, the terms are used interchangeably. <<

Only by the ignorant :) The same guys that confuse number and numerals, and do not understand why their inability to abstract prevented them from learning mathematical concepts.

>> Of course we can create a "record" which is not a physical row, but how do you know that your physical row is a physical row? <<

No, a record has to be physical. The term “physical row” makes no sense. These layers of abstraction are important for people who work with ideas, wrote optimizers, etc. I will get back to this.

>> Your entire paragraph about fields vs columns is flawed. Neither can exist in SQL Server without a physical existence. Even a computed column. <<

Sorry, but a computed column, view or a query result comes into existence at invocation. It has no existence before that. Consider two sessions which reference the same VIEW. It might be materialized as one shared table in working storage or a single file on a disk. OR it might be a copy of text inserted into many different parse trees. And once it is in the parse tree, show it to me. After all, it has tobe physical :)

Does a computed column still have a physical existence if I do not use it, as you say?

If I ask for the TOP(n) rows in a query, where are the rest of the rows?

Is a recipe the same as a cake? Starting to think abstractly yet?

>> "a file is usually data that deals with a whole business process" -- Say what? You don't actually believe that do you? Does a SQL Server log (ldf) file contain an entire business process? Does a flat file containing addresses contain an entire business process? Does a Key file that contains an encryption key only contain an entire business process? <<

The “unit of work” in RDBMS is a schema. A properly designed schema is a model of a “Universe of Discourse” and you connect to that schema. Files are not interrelated like that. When I update my phone number in the Personnel file, the health plan file does not know about it. One process (personnel data) and not the whole enterprise. When I update my phone number in the Personnel table, the reference in the Health_Plan table will use it when I next do a SELECT on it.

>> "The payroll would now have separate tables for timecards, employees, projects and so forth" We agree on a point wow! I bet you read that somewhere didn't you? <<

Actually, I wrote it in several of my books :)

>> "Thus "Employee" is a bad name because it is singular; "Employees" is a better name because it is plural" -- your opinion. I prefer singular, but it makes no real difference. I like folks to be consistent though.<<

No. It is ISO-11179 meta data naming rules. IDEF was a modeling system for file systems where the work in done with record-by-record processing (read: COBOL files) and that used singular record names. After 20+ years of fixing Cowboy Coder SQL, I use it as a symptom of bad SQL. It tells me the mindset cannot handle the abstraction of sets.

A problem particular to the SQL language is the name space. Tables and columns can have the same name! I will not even get into constraint names and other things.

>> "The use of NULLs in SQL is also unique to the language" -- rubbish. I can name a whole bunch of languages that support the idea of a NULL. In fact, I'm struggling to think of one that doesn't, though I'm sure they exist. <<

Please quit struggling and just name one such language :)

Fortran? C? Cobol? ADA? C++? I can think of all the ANSI X3J languages and a dozen others that I have used. In the last 35+ years. Nope, no NULLs, no 3 valued logic unless you count proprietary 4GLs that came with SQL products – Informix 4GL, PL/SQL, SQL/PSM, T-SQL, Progress, Base-10, etc.

>> "Nor do fields have constraints which can be added to them in the record, like the DEFAULT and CHECK() clauses in SQL" Nonsense. Of course they can. In ANY mathematical, scientific, or programming model, one is free to make certain rules to make the data meaningful. <<

Wrong. You can write application code for these things (and pray that every one of the programs, present and future, do it the same way). Columns have these things as part of their definition. This was one reason we moved from files to DBMS. When all records were 80 characters in EBCDIC because of punch cards, you could read totally unrelated tapes into application programs and get a printout. What happens when I do a SELECT with the wrong table in the FROM?

Programmers always missed something or failed to properly update hundred of application programs when the rules changed. Data quality was a bitch.




--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-07-21 : 20:27:23
Thanks for responding. I thought you had me on invisible mode.

>> A "Standard" is a set of recommendations, built collaboratively by various companies and scholars. As such, it is NOT a set of rules. In the real world standards are almost never followed. Each vendor builds in their own proprietary syntax, functions and rules. This is a good thing. And this is exactly what happens in a free market economy. <<
Sorry, Standards are often required by law and carefully defined by experts. Is a metre a matter of opinion in your world? In the case of SQL, the vendors were the guys pushing for the ANSI X3H2 committee to lock down SQL so it would not go the way of BASIC.
Do you remember when vendors did have their own programming languages? Auto-coder? Dibol? RPG? It sucked. You might want to read a history of programming languages and the Metric system. Before ISO and the modern world, every local governmental unit invented their own changeable system of weights and measures, without any precision to them and made modern capitalism almost impossible.

Vendors still have their own programming languages. C# and VBScript are two examples.

We have “Cowboy coders” who deliberately try to write lots of dialect to secure their jobs. It sucks and that is why large companies have coding standards. It is why professionals write maintainable code and Cowboys don't.

Remember about the time that Microsft started making Netscape obsolete when web developers were up in arms about IE not being “standards compliant?” They were upset about things like IFRAMEs and the DOM not complying with Netscape or the W3C. Today, those ARE the standard.
But in a free market economy, the way that software vendors distinguish themselves is by adding features that others don’t have – whether it complies with the “standard” or not. In SQL Server, for example, I can use getdate() or CURRENT_TIMESTAMP. They mean the same thing. One will port to DB2, the other won’t. Who cares? If I’m writing code against SQL Server, it needs to work on SQL Server. Should I be concerned if my SQL Server CTE won’t port to Oracle? For 99.9% of programmers, the answer is absolutely not.
Regarding standards, regardless of your opinion, what I stated holds true. Standards are a “recommendation” not a “rule” and to argue otherwise is to ignore the facts of the real world. Again, this is a good thing. Else, we’d still all be Cobol programmers.

>> In SQL Server, there is a clear distinction between tables, views, CTEs, query results. None of these are tables except for tables. <<

Wrong. I can use a properly written virtual tables anywhere I can use a base table. They have the same data types and constraints on their columns, etc. That is why I can use them together.

When we had files with various formats such as HDAM, PHDAM, HISAM, ISAM, HIDAM, PHIDAM in just the IBM world, we had use different functions to get data out of them. I also had to do explicit type conversions and a lot of other things. These things REALLY were distinct!

This has nothing to do with the discussion. Philosophically, any result set can be called a table. But what SQL programmers consider tables are exactly what I mean when I say table – an entity, persisted to disk (or memory) that contains the elements we mean when we write a CREATE TABLE statement. Nothing else is a table to a SQL programmer.

>> Your distinction between rows and records is not quite correct. In real life, the terms are used interchangeably. <<
Only by the ignorant :) The same guys that confuse number and numerals, and do not understand why their inability to abstract prevented them from learning mathematical concepts.
>> Of course we can create a "record" which is not a physical row, but how do you know that your physical row is a physical row? <<
No, a record has to be physical. The term “physical row” makes no sense. These layers of abstraction are important for people who work with ideas, wrote optimizers, etc. I will get back to this.

Here you’re way off. And, perhaps, contradict your earlier statements. If we’re going to distinguish (which we shouldn’t) a record is one row of a result set. Abstractly, we have no idea where the elements in the record came from. And if we assume that they are from a physical row, well, we’re probably wrong because one of the “fields” or “columns” could be the result of a join, or the result of a pointer in our “physical row” that said “I moved, my new address is here – go look there” not a physical row/record.

>> Your entire paragraph about fields vs columns is flawed. Neither can exist in SQL Server without a physical existence. Even a computed column. <<

Sorry, but a computed column, view or a query result comes into existence at invocation. wrong. It comes into existence as soon as data is inserted into the table containing it. It has no existence before that. Consider two sessions which reference the same VIEW. It might be materialized as one shared table in working storage or a single file on a disk. OR it might be a copy of text inserted into many different parse trees. And once it is in the parse tree, show it to me. After all, it has tobe physical :)

It is physical on disk. Once it’s loaded into memory, it can still be considered physical. But even if you want to call it virtual, it is the same thing to every session that “sees” it.

Does a computed column still have a physical existence if I do not use it, as you say? of course it does


If I ask for the TOP(n) rows in a query, where are the rest of the rows? in memory, on disk. What’s your point?

Is a recipe the same as a cake? Starting to think abstractly yet? The problem isn’t my ability or lack thereof to think abstractly, the problem is your insistence on an imaginary set of rules that has little to do with real world problem solving or data storage and retrieval.

>> "a file is usually data that deals with a whole business process" -- Say what? You don't actually believe that do you? Does a SQL Server log (ldf) file contain an entire business process? Does a flat file containing addresses contain an entire business process? Does a Key file that contains an encryption key only contain an entire business process? <<

The “unit of work” in RDBMS is a schema. A properly designed schema is a model of a “Universe of Discourse” and you connect to that schema. Files are not interrelated like that. When I update my phone number in the Personnel file, the health plan file does not know about it. One process (personnel data) and not the whole enterprise. When I update my phone number in the Personnel table, the reference in the Health_Plan table will use it when I next do a SELECT on it.

So you agree with me apparently here. Of course, a unit of work in an RDBMS is not a schema. It’s a query. A schema is an abstraction that can’t, by definition, perform any work. Nothing we do as data architects matters one little bit until someone tries to interact with the data.

>> "The payroll would now have separate tables for timecards, employees, projects and so forth" We agree on a point wow! I bet you read that somewhere didn't you? <<
Actually, I wrote it in several of my books :)
>> "Thus "Employee" is a bad name because it is singular; "Employees" is a better name because it is plural" -- your opinion. I prefer singular, but it makes no real difference. I like folks to be consistent though.<<
No. It is ISO-11179 meta data naming rules. IDEF was a modeling system for file systems where the work in done with record-by-record processing (read: COBOL files) and that used singular record names. After 20+ years of fixing Cowboy Coder SQL, I use it as a symptom of bad SQL. It tells me the mindset cannot handle the abstraction of sets. Again, a standard is a recommendation, not a set of rules. Today, many engineers and data architects prefer their tables to represent a singular version of what is stored in it. Really, it matters not at all, as long as folks are consistent. In fact, the ONLY thing that matters with naming conventions is consistency. You have your preferences, I have mine. We’re both right! If I can understand your code in context that’s all that matters.

A problem particular to the SQL language is the name space. Tables and columns can have the same name! I will not even get into constraint names and other things.
>> "The use of NULLs in SQL is also unique to the language" -- rubbish. I can name a whole bunch of languages that support the idea of a NULL. In fact, I'm struggling to think of one that doesn't, though I'm sure they exist. <<
Please quit struggling and just name one such language :)

Surely you jest. C++, VB, C, Java, JavaScript, BASIC, C#, RPG, Ruby, off the top of my head. There are a lot more. Please tell me ONE language that requires you to initialize a variable with a value upon declaration. Any that doesn’t, supports NULLs. By definition. There are immediately 3 states to a bit. On, Off, Unkown.

Fortran? C? Cobol? ADA? C++? I can think of all the ANSI X3J languages and a dozen others that I have used. In the last 35+ years. Nope, no NULLs, no 3 valued logic unless you count proprietary 4GLs that came with SQL products – Informix 4GL, PL/SQL, SQL/PSM, T-SQL, Progress, Base-10, etc.
>> "Nor do fields have constraints which can be added to them in the record, like the DEFAULT and CHECK() clauses in SQL" Nonsense. Of course they can. In ANY mathematical, scientific, or programming model, one is free to make certain rules to make the data meaningful. <<

Wrong. You can write application code for these things (and pray that every one of the programs, present and future, do it the same way). Columns have these things as part of their definition. This was one reason we moved from files to DBMS. When all records were 80 characters in EBCDIC because of punch cards, you could read totally unrelated tapes into application programs and get a printout. What happens when I do a SELECT with the wrong table in the FROM?

Again, WRONG. If you build it in at the base level, it doesn’t matter what the program supports. In IT our two primary responsibilities are (1) data reliability and (2) data availability. Everything else we do is icing on the cake. If we can’t promise good data, nothing else we do matters. Adding constraints at the lowest level we can isn’t just good programming – it’s essential.

Programmers always missed something or failed to properly update hundred of application programs when the rules changed. Data quality was a bitch. bad programming is always a bitch
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-07-22 : 11:38:27
quote:
Originally posted by russell

Aleph, do not be fooled by Joe's self-promoting bs. He barely understands the subject himself in the real world. He's been teaching SQL for 20 years? Scary. I bet SQL USA was his best student!

The terms "row" and "record" are interchangable.
The terms "field" and "column" are as well.

As Gail said, it's semantics. Give me a few minutes to read the entire hunk of garbage he posted and I'll bet I find a lot more horse crap in there.
I disagree about it being just semantics. You may not agree with Joe or like his style, but he does have a background in the area. I may not agree with him all the time, but this is one area that I do agree with. I deal with people all the time that work in the data industry and have no idea the distinction between fields and columns, etc... And, honestly, people that are data professionals, and don't know the different and use the wrong term sound rather foolish. In a similar way that "data professionals" who don't know the different between data and information are hard to take seriously. But, then again I’ve worked with people that have been or claim to be database architects and they can’t tell me the difference between first and second normal form. Is knowing the difference between normal forms just semantics too? Absolutely not!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site