-1

I have two tables, Police and Violations [Police holds the data of policemen, and Violations contains the data of all parking violations], and the essential idea is that a police can cancel any number of Violationss but, only one Violations canceled by a single police.So essentially, it could be seen as (police PK(username)) 1:M (Violations PK(Violations#)) Now, the Violations table will have the username of the police as a Foreign Key. Now if the one of the police cancels the specific Violation, then the username of this police , date and time of cancel, will also be added rather than this it will have a null value in Violations table.

The problem is when a Violation inserted into the database the field belong to that police in Violations table is NULL and I wish to avoid this NULL value. [ I mean, (username, date, time) will have value only if police cancel a Violations which is mean update the status of Violations to cancel. ]

Chandrahas Aroori
  • 955
  • 2
  • 14
  • 27
Reem
  • 23
  • 1
  • 6
  • 1
    Hi. You are still not clear about your design or question. You put a nullable column in your design but you don't want a nullable column--so why put it in? If you don't know how to model this without using nulls & you want to know how then please *edit your question to say that*. (And it's not a change that is normalization.) Right now you don't clearly express your "problem". But per my first comment, before posting "Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers." PS Please edit clarifications into posts, not comments. – philipxy Mar 05 '18 at 21:36

3 Answers3

5

the police can cancel many violations but one violation canceled by one police so, it is (police PK(username)) 1: M (violation PK(violation#)).

No, it is 0-or-1 to M.

So, violation table will have the username of the police as FK.

Only if you want to allow NULL as username.

If you don't, you make a separate table for cancelled violations.

 police(username, ...)
 violation(violation#, ...)
 cancelled(violation#, username, ...)

You never need to use nullable columns. Just identify what sort of things you want to say about your application/business situations that will collectively fully describe them.

Time to read a book on information modeling, the relational model & database design.

Re normalizing given nulls.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • thank you yes as you said 0-or-1 to M. but does your solutions refer to any rules of normalization. Or I can do this solution even doesn't stand to any FD. and what about the relationship it will change which is not the same will i did it in mapping ? – Reem Mar 06 '18 at 04:39
  • 1
    I said, your question is not clear. I answered it since it seemed you meant "what is a null-free version of this design". I said, the choice has nothing to do with normalization. If you think it does then please post a new question explaining clearly & fully where & how you are stuck following some reference textbook on how to normalize. Please give as much of a [mcve] as you can. I cannot understand much of the language in your comment though. PS [Re normalizing given nulls.](https://stackoverflow.com/a/40733625/3404097) – philipxy Mar 06 '18 at 07:08
1

It sounds as though you are talking about an optional relationship (0 or 1 to M). A NULL in an FK is a fairly standard way of indicating that a relationship is not present.
In this case, it means that there is no cancelling police officer because the violation is not cancelled. Right? I don't see why nulls are a problem here.

But, if you want to get rid of NULLS here, there is a fairly simple way to do it. Just create a relationship table, containing FKs to both of your tables. And remove the FK from the violations table. Now, instead of having a NULL anywhere, just don't insert a row in the relationship table! Later on, when a cancellation does occur, just insert a new row in the relatonshp table.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
-2

If you don't want nulls in the "cancelling police officer" FK column, you must link to an officer even if the violation is not cancelled.

The only solution is to have a special fake police officer to indicate "not cancelled", perhaps one whose username and bio data is blank.

Wanting to avoid nulls is a bad design choice. The correct, easiest, clearest and universally accepted way of handling this is to put a null in the FK column when a violation is not cancelled.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 3
    Every assertion you make here is a fallacy. – onedaywhen Mar 13 '18 at 14:41
  • @onedaywhen how so? Perhaps you could enlighten me. – Bohemian Mar 13 '18 at 17:31
  • 2
    See the answer by @philipxy to see why your first sentence is a misstatement. Avoiding nulls is the correct design choice; SQL's three-valued logic is unintuitive, unnecessarily complex and inconsistently implemented. Many others avoid SQL nulls, therefore your "universally accepted" assertion is untrue. I'm not sure I've ever seen a SQL problem with only one solution! As an aside, while I've used the null object pattern in C#, your "special fake police officer" suggestion sounds awful. – onedaywhen Mar 16 '18 at 13:47