- Local time
- Today, 02:50
- Joined
- Feb 28, 2001
- Messages
- 27,317
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.