sql - How can I avoid NULLs in my database, while also representing missing data? -

in sql , relational theory (c.j. date, 2009) chapter 4 advocates avoiding duplicate rows, , avoid null attributes in data store. while have no troubles avoiding duplicate rows, struggling see how can model data without making use of null. take following, example - bit work.

we have artist table, has, amongst other columns, gender column. foreign key gender table. however, artists, don't know gender - example we've been given list of new music has no descriptions of artist. how, without using null 1 meant represent data? solution see add new gender, "unknown", gender table.

while thoroughly enjoying book, disappointed when chapter concluded with:

of course, if nulls prohibited, missing information have handled other means. unfortunately, other means complex discussed in detail here.

which real shame - because solution waiting read about! there reference read appendix has lots of publications read, hoping little bit more of down earth summary before dived reading these.

---

i'm getting few people commenting don't understand why wish avoid 'null' quote book again. take following query:

select s.sno, p.pno   s, p  s.city <> p.city     or p.city <> 'paris' 

now, take example s.city london, , p.city paris. in case, london <> paris, query true. take case p.city not paris, , infact xyz. in case, (london <> xyz) or (xyz <> paris) true. so, given data - query true. however, if xyz 'null' scenario changes. in case both of these expressions neither true nor false, in fact, unknown. , in case because result unknown not rows returned.

the move 2 value logic 3 value logic can introduce bugs this. infact, introduced 1 @ work motivated post. wanted rows type != 0 however, ends matching type == 0 or type null - confusing behavior.

whether or not model data or without null in future unclear, i'm curious other solutions are. (i have been of argument if don't know, should use null).

everybody's talking , no 1 except dportas , walter can understand question. ok, 95% of people on not understand null problem, , feel threatened because databases full of nulls, want convert seeker. priceless. how going learn when arguing ?

good on you, eliminating nulls. have never allowed nulls in of databases.

of course, if nulls prohibited, missing information have handled other means. unfortunately, other means complex discussed in detail here.

actually not hard @ all. there 3 alternatives.

1. here's paper on how handle missing information without using null h darwen, may head around problem.

1.1. sixth normal form answer. not have normalise entire database 6nf. each column optional, need child table off main table, pk, fk, because 1::0-1 relation. other pk, column optional column.

look @ data model; assetserial on page 4 classic case: not allassets have serialnumbers; when do, want them store them; more important want ensure unique.

(for oo people out there, incidentally, 3 level class diagram in relational notation, "concwete table inheritance", no big deal, we've had fro 30 years.)

1.2. each such table, use view provide 5nf form of table. sure, use null (or value appropriate column) identify absence of column row. not update via view.

1.3 not use straight joins grab 6nf column. not use outer joins, either (and have server fill in null missing rows). use subquery populate column, , specify value want returned missing value (except if have oracle, because subquery processing worse set processing). eg. , eg. can convert numeric column string, , use "missing" missing rows.

when not want go far (6nf), have 2 more options.
.
2. can use null substitutes. use char(0) character colomns , 0 numeric. not allow fks. need value outside normal range of data. not allow 3 valued logic.
.
3. in addition (2), each nullable column, need boolean indicator. example of sex column, indicator sexismissing or sexless (sorry). allows tight 3 valued logic. many people in 5% because db remains @ 5nf (and less tables); columns missing info loaded values never used; used if indicator false. if have enterprise db, can wrap in function, , use udf, not raw column.

of course, in cases, can never away writing code required handle missing info. whether isnull(), or subquery 6nf column, or indicator check before using value, or udf.

if null has specific meaning ... not null ! definition, null unknown value.