Nulls: Should it ever be meaningful? (2 Viewers)

[The_Doc_Man]

Then why they say that a ideal database should adopt a closed world assumption; that it should have all data that it will ever need for its purpose?

If you never have nulls in any single-table record - i.e. all records are properly filled before you finish data entry; and if all JOINS are INNER joins; and if no query is ever allowed to take direct user input on search objects without first being tested separately for an existing population; then you should never see a NULL. If you never see a NULL, that is the closed world assumption, and it occurs because you took pains to fill in all the data you will ever need....

which necessitates a need to represent a empty set, which leads us to null... correct?

Unless you take the pains described above. But hoo boy is the above a tough place to reach. But if there is ever a time when you could be exposed to a NULL then yes, correct. (Which is why I sometimes push hard for taking the pains to avoid them.)

At risk of derailing the thread

Shoot, I have a one-track mind to begin with, and at my age I'm lucky the train is still on the track. There's so much loose rust that I sometimes understand why folks think of me as a bit flaky.

1) Users and Group, 2) Groups and User. (I think the plurality here is deliberate).

Many users can be in a group. A user can be in many groups. What you MIGHT be looking at is the JUNCTION table of Users and Groups and you can choose to look from either the left side or the right side of the junction. As a whole, the Users and Groups tables and their junction would be a proper case of a many-to-many relationship, but when looking through the microcosm of that "collection" interface, you obscure the actual structure. I don't know that I agree with this being denormalized without more data. I suspect that the collection interface hides a lot of sins.

Regarding your comments about a list of users when you already might have a list of employees. You are wondering whether this is a waste of space or a violation of normalization. Well, damned if you don't ask some real conundrums, but I have to think that this represents a special case.

First, the Access user tables were invented first and are encoded "behind the scenes" - so you would have one helluva time melding them into the employees table. Second, suppose that you have to permit someone to use the database who is not an employee - perhaps because you have a consultant or contractor. Third, if this were an inventory database, you would still need that user table because it happens to involve metadata - namely the permissions needed to exercise Access activities. While I don't disagree that for a personnel database, one could define a role "User of the whatchamacallit database" - Access can't take the chance that you would do what it needs in that database. So it creates what you see as a redundancy. I'm not sure that from the purist viewpoint you are wrong. This is therefore a pragmatist's reason for not allowing that overlap.

 

[The_Doc_Man]

Don't know about this thread, but my mind is derailed. It is pushing midnight here in N'Awlins. I'm outta here.

 

[Banana]

Unless you take the pains described above. But hoo boy is the above a tough place to reach. But if there is ever a time when you could be exposed to a NULL then yes, correct. (Which is why I sometimes push hard for taking the pains to avoid them.)

Gotcha. It makes all sense. At least for the month.

Many users can be in a group. A user can be in many groups. What you MIGHT be looking at is the JUNCTION table of Users and Groups and you can choose to look from either the left side or the right side of the junction. As a whole, the Users and Groups tables and their junction would be a proper case of a many-to-many relationship, but when looking through the microcosm of that "collection" interface, you obscure the actual structure. I don't know that I agree with this being denormalized without more data. I suspect that the collection interface hides a lot of sins.

It's quite possible. This is the diagram of DAO I mentioned earlier:

Unless the model is hiding something, there's no junction tables between users and groups, and naturally I'm a bit curious as to why this is structured thus.

While I don't disagree that for a personnel database, one could define a role "User of the whatchamacallit database" - Access can't take the chance that you would do what it needs in that database. So it creates what you see as a redundancy. I'm not sure that from the purist viewpoint you are wrong. This is therefore a pragmatist's reason for not allowing that overlap.

Yes, I suspected that this was a meta-data (?) needs overriding normalization.

Don't know about this thread, but my mind is derailed. It is pushing midnight here in N'Awlins. I'm outta here.

You know, the last time I checked, this was a forum where you post at your leisure, not a Database 911 dispatch.

Anyway, good night (or good morning by time you're reading this post! )

 

[Banana]

If you never have nulls in any single-table record - i.e. all records are properly filled before you finish data entry; and if all JOINS are INNER joins; and if no query is ever allowed to take direct user input on search objects without first being tested separately for an existing population; then you should never see a NULL. If you never see a NULL, that is the closed world assumption, and it occurs because you took pains to fill in all the data you will ever need....

In my earlier post, I said it made all sense, but after thinking about it; it occurred to me that it didn't mesh right if we're calling it an 'assumption'. Assumption is something you postulate at the *start*, not after the fact. In the scenario you described, this is no longer an assumption, but rather... (trying to find the right word) a fact (probably not right word, but hope you understand what I mean) about this database.

 

[The_Doc_Man]

I could nit-pick about assumptions and postulates and such but that would be arguing for the sake of argument. 'tain't worth it.

On the other hand, the DAO model that you described is merely the "public" aspect of the overall model. If you understand how languages make things public and private - properties and methods in the Access case - you would realize that the tables underlying Users and Groups, precisely because to a large extent they ARE metadata, might be private and the part you see is merely a view (SELECT query) made public through the collections interface. If you were allowed direct access to the structure, you would be able to change object security. Using the collection interface, there is code standing between you and the "real" data being collected for you.

You don't think those collections are arrays, do you? Those are FUNCTIONS (methods) that are smart enough to accept multiple types of data input, including numeric indexes and object names. What they return is a pointer to a structure that has its own objects, properties, and methods.

See, I wouldn't trust the collections interface to EVER tell you the truth about underlying structure. Only underlying CONTENT. Because it is not the purpose of a collection interface to provide you with meta-data. Only with ordinary data. In fact, there is an argument that says they don't DARE give you the reality of what you are seeing. If you look at the collections that make up the COM interface in Word or Excel, you run into this again and again.

For instance, in Excel, if you wanted to get to a particular cell, you have some choices...

Workbooks.Worksheets(m)..... is the first part of the locating sequence. Call that part ActiveSheet for the moment, which is Excel's shortcut equivalent to Me. in forms and reports.

Cell B3 of your active worksheet is...

ActiveSheet.Columns(2).Cells(3)

or

ActiveSheet.Rows(3).Cells(2)

or

ActiveSheet.Cells("B3")

But these all select the same thing! How can there be three distinctly different paths to the same thing - unless the collections interface cannot be trusted to reflect true underlying structure?

I think that the Users/Groups structure is as I described but the interface to it just imposes a structure that is the public face of what you see.

 

[The_Doc_Man]

but rather... (trying to find the right word) a fact (probably not right word, but hope you understand what I mean) about this database.

The French might call it a fait accompli (probably with more than a couple of diacritical marks over the vowels.) An ACCOMPLISHED fact. Implying that it didn't just happen. Somebody DID something to get there.

 

[The_Doc_Man]

this was a forum where you post at your leisure, not a Database 911 dispatch.

Yes, but finding a cogent, serious discussion with someone who knows enough to wipe the boogers off his lip occasionally is so refreshing.

I don't want to seem elitest because I really do enjoy helping others. But after a while it becomes tough to repeat the UNIX mantra, RTFM. (Read the Friggin' Manuals.) Folks don't appreciate that this forum's search AND a GOOGLE search can help find all sorts of help. Some folks don't want to help themselves. They want someone to mollycoddle them.

I guess my pet peeve along those lines is that without fail, in late October and again in late April, we see a dozen posts from different people on topics that repeat with SUCH regularity that they can ONLY be class assignments. As if any of us care whether a chowderhead too stupid to RTFM or pay attention in class should get any help whatsoever. But then I have to remember that there was a time when I, too, was a chowderhead. It just hasn't been recent. Maybe that's why I get grouchy sometimes these days. It's harder to remember when I was still wet behind the ears.

Sometimes I think that if I see one more "Used Car Salesman" database or "Book this Room" database, I'll scream. Do we have a "smiley" for that?

 

[Banana]

Sometimes I think that if I see one more "Used Car Salesman" database or "Book this Room" database, I'll scream. Do we have a "smiley" for that?

Totally understandable.

The thing is, I actually envy those "chowderheads", because they're actually in a class and receiving benefit of a formal training (unless, of course, the teacher is an ignorant smut). I'm pretty much self-taught, and while that's a good thing if we're talking about self-sufficiency and ability to research the needed information and translate it in something that apply to one's particular problem.

But I've come to believe that there is a serious deficit in being self-taught; it is all too easy to be focused on finding the solution of the problem, at expense of not actually understanding the concept sufficiently, *especially* if there's another similar problem that looks vastly different but actually be solved using similar methods.

Heck, I can tell you that I've read up on several articles about normalization, and still get tripped up, like that three-legged table idea we discussed earlier. Sometime I think that if I had a proper class curriculum about database theory, I'd never have had thought up that three legged tables and suffered by it.

But how typical- you never know what you're missing until you actually need it.

 

[The_Doc_Man]

Want to know a secret? I'm self-taught.

My formal college-level computer classes? Two. FORTRAN and Operating Systems. All else was learned at the school of hard knocks.

Now, I DID take a few vendor-provided classes, but they don't apply to Access. They were for the VMS Operating System. I have taken one class in DB Administration from ORACLE and one from SmartStar Corp. (now defunct in a big fish-little fish buyout). That's it, the sum and total of all my "taught" courses. All else is self-taught.

We have SO MUCH available with the big-I Internet. Between some really good book clubs and some really good searches through Alta Vista, I learned a lot. It is possible. It takes some self-discipline to set aside the time, but it is possible to do so.

 

[Banana]

tblPerson
PersonID - PK
Info about person

tblRole
RoleID - PK
descriptive information about role

tblPersonRole
PersonID - FK to person
RoleID - FK to role held by that person
(This might be all you need here unless you are tracking when this person was given this role, or when this person was relieved of this role.)

tblProperties
PropertyID - PK
descriptive information about property - contains the "question" that you need to answer.

tblRoleProperties - lists attributes/properties/"questions" applicable to this role
RoleID - FK to role
PropertyID - FK to property
This lists the applicable questions to each role. THIS defines the properties of the role. The tblProperties table only lists properties used for at least one role, but no guarantee about how often it is actually used. (But then there is always DCount...)

tblPersonRoleAnswers
PersonID - FK to person
RoleID - FK to role
PropertyID - FK to specific property
Answer - specific to the question for this person as applicable to this role.

Just so I'm following you; all foreign keys should go directly to the originating tables, not from junction tables?

 

[Banana]

Want to know a secret? I'm self-taught.

Wow. I'd never have thought, especially seeing how we've discussed set theory, interrogative logic, the concept of nulls, among host of other heady topics. Though I recall you saying you took more math classes than you cared; so that'd have helped some, no?

Anyway, you're right; there's a wealth of information out there, and I shudder to imagine me doing all this work without the benefit of this forum, the internet.

Believe me, I'd be *still* violating 1st Normal Form for last year if I didn't have that access!

 

[The_Doc_Man]

Just so I'm following you; all foreign keys should go directly to the originating tables, not from junction tables?

But tblPersonRoleAnswers IS a junction table. Three-way junction. A foreign key ALWAYS must point back to the table where that key is prime. Otherwise it is not a foreign key.

~~~~~~~~~~~~​

As to being self-taught, my graduate school minor was very mathematically oriented and some set theory was part of my training. You use it in quantum mechanics when working on the set of all atomic and molecular orbitals in your substance of interest. So the math is there. Lots of statistics and even a touch of matrix operations. (Hated the latter; tolerated stats pretty well, though.)

It is the database stuff that I watched grow. Believe it or not, I was in computers before there was a commercial version of SQL. At that time, all databases were flat files. So I recall reading some of the articles on relational theory with great interest. I followed up the formal logic, both assertoric and interrogative, pretty much on my own except for a couple of basic classes.

The database stuff I learned on a project for my first employer. Heck, we wrote a database driven real time control system based on an old PDP-11 series running RSX-11M as its O/S. Real-time is, of course, relative. When the physical processes take two minutes for a mechanical object to close, even a slug-a-bug of a computer can keep up. I personally wrote the database manager kernel, which fit in 8K bytes not counting dynamic memory buffers. It also did the equivalent of virtual demand paging with page retirement based on locks and least recent use. Darn thing worked for years in about 60 sites before the last one finally got replaced by something more modern. Back then I was a serious bit-twiddler, so dealing with properties and methods now isn't so very nasty.

I won't say I'm older than dirt, but I will say that dirt and I are on a first-name basis with each other. And with age comes experience. With experience, if you are awake for some of it, comes knowledge.

I got into the computer field originally because in my chemistry training, I realized that most of my professors hadn't grasped the potential of computers. They were off in their ivory towers. My minor professor knew what was going on. My major advisor had a clue. But the rest of my committee was clueless. It gave me the idea that he who could talk both to people and to computers would never be out of a job. That has been nearly true except for 3 months during a time when I was going through a major family crisis. Now, I have fewer years ahead than behind. I hope to pass along at least some of what I learned, even if in less formal ways.

 

[Banana]

But tblPersonRoleAnswers IS a junction table. Three-way junction. A foreign key ALWAYS must point back to the table where that key is prime. Otherwise it is not a foreign key.

Cool. I am bit embarrassed to admit this but I've always shyed away from using compound keys mainly because there seemed to be a conspiracy to continually tell users how to create one, but almost never how to use it! So I was kind of in dark about how compound keys are supposed to work (e.g. does Access concatenate it together? If I use the table in a query, do I need both field? Plus few more I can't think of right away.

And that's quite a story! Now I understand how you ended up where you are with the Ph.D. in Chem. One more silly question: Since you are in Navy, it's customary to call you Sir. But you're also a Doctor. Does that mean we have to call you Dr. Sir The_Doc_Man?

 

[Banana]

Returning to the topic on hand....

Why have Nulls?

Learning to handle Nulls can be frustrating. Occasionally I hear newbies ask, "How can I prevent them?" Nulls are a very important part of your database, and it is essential that you learn to handle them.

A Null is "no entry" in a field. The alternative is to require an entry in every field of every record! You turn up at a hospital too badly hurt to give your birth date, and they won't let you in because the admissions database can't leave the field null? Since some fields must be optional, so you must learn to handle nulls.

Nulls are not a problem invented by Microsoft Access. They are a very important part of relational database theory and practice, part of any reasonable database. Ultimately you will come to see the Null as your friend.

Think of Null as meaning Unknown.

Source

This got me thinking- I do actually have entries in some of my lookup tables as "Unknown", "Unspecified", or "Not disclosed" or whatever. While I'd have needed them anyway, this lets me to enforce RI for *all* of my relationship and not worried about Access refusing an entry because an user didn't have the answer at the moment as I've already provided them the back door precisely for such contingency.

Does that mean I'm overconstraining my database here? Last year, I was pretty bruised up after designing my first without any understanding of normalization, so I decided to make everything tight as I could possibly, even if it created additional headaches for me.

 

[The_Doc_Man]

Allen and I disagree slightly here.

NULL does not mean UNKNOWN in a given field not determined through an OUTER JOIN. If you opened the record, then your design should allow you to have a blank - NOT A NULL - for any optional field lacking a response. Or you should have a flag or state variable that shows that incomplete information was available at original entry and an override of data entry rules was allowed.

The guys in the office just call me "Doc" and let it go at that. I was never in the Navy, I'm a civilian contractor for the Navy. I was never an officer or anything like that. I've ALWAYS worked for a living. (That's a little Navy humor, if you're not sure, just ask.)

I am not sure why you think you might be overconstraining the DB. Allen's comments about NULL diverge from mine. While I respect Allen's opinions, I think this time he's SLIGHTLY off the mark. UncleGizmo said I was a bit harsh regarding this issue, but Allen is not using NULL correctly. He is taking a lazy man's way out. If your database doesn't depend on OUTER JOIN queries then there is no excuse (in my mind) for seeing a NULL in any record that has been at least partly defined. But that's just me.

 

[Rabbie]

Hi

I have been following this discussion with interest and I have a quick query about Nulls. Is it acceptable to allow a null entry in an address line entry. After all some addresses are shorter than others. Here in the UK somepeople will have a house number while other will just have a house name.
so the street name may appear in either the first address field or the second.

To allow for this I have empty fields in some records.

I welcome your comments.

 

[Banana]

Just so I'm understanding this correctly:

Let us consider a field. It has no default value. It is set to required.

A new record containing this field is loaded.

Is the field in NULL state until it's filled or should it be called "empty"?

 

[Rabbie]

As an old man I don't always make myself as clear as I would like.

In my address table I have 4 fields for the address One for each line + 1 for the post code.

Some addresses do not use all the fields and sometimes the Post Code is unknown so these fields are left "empty" or "null".

So I finish up with empty/null fields in the record.

I guess it is not really that important as the fields are not used as an index - just used to populate fields in a report.

 

[CraigDolphin]

If anybody is watching this discussion unfolding, please feel free and join in. Two great minds are great, but three are better, and four is even better! So come down and speak your piece of mind here!

It's like watching two gladiators wrestling with a giant anaconda! Seriously though, I'm enjoying (and trying to learn from) the conversation even if I can barely follow parts of it.

In my earlier post, I said it made all sense, but after thinking about it; it occurred to me that it didn't mesh right if we're calling it an 'assumption'. Assumption is something you postulate at the *start*, not after the fact. In the scenario you described, this is no longer an assumption, but rather... (trying to find the right word) a fact (probably not right word, but hope you understand what I mean) about this database

About the only thing I might offer an opinion on is that assumptions come in two flavors in experimental statistical analysis: a priori (before the experiment) and a posteriori (after the experiment results are out) so you can call it an a posteriori assumption without any hesitation in my book. The terms are latin http://en.wikipedia.org/wiki/A_priori_and_a_posteriori_(philosophy)

 

[Banana]

I guess it is not really that important as the fields are not used as an index - just used to populate fields in a report.

Correct. The_Doc_Man talked about how Nulls are a problem when you do a Outer Join query. There's no problem here because you still have a key for the record, even if some fields are blank. So Access can pull up that record (whether as a form or as a report) with no problem because it has the key. (hmmm... double meaning here... )

You'd have bigger problem if you allowed the key to be null, though. I don't know if The_Doc_Man would agree, but this is a problem with a foreign key; Access defaults a Number data type to 0 (zero), and sets Required to No. Not wrong thing when it's a plain number data type, but when it's a foreign key, Allen recommend (and I concur) that default value be omitted, and required set to yes. This way, we avoid creating orphaned records.