Default NULL for foreign key vs default 1

[kuato]

Stupid question here. Let's say you a simple setup where users submit articles and an article only belongs to one category. Categories are N level deep using the parent_id concept.

The categories table:

create table categories (
    cat_id SERIAL PRIMARY KEY,
    parent_id default NULL,
    cat_name varchar(255)
);
CREATE INDEX parent_id_idx ON categories(parent_id);

Here you definitely want the parent_id to default to NULL to indidcate that by default all new categories belong to root until otherwise assigned. And of course we can get all the top level categories with:

SELECT * FROM categories WHERE parent_id IS NULL

Now with the articles table it's possible a user can create an article but not assign it a category right away because he/she is not really sure which category it falls under.

create table articles (
    article_id SERIAL PRIMARY KEY
    user_id INTEGER NOT NULL,
    cat_id INTEGER default NULL,
    article_title varchar(255),
    article_text TEXT,
    CONSTRAINT cat_id_exists FOREIGN KEY(cat_id) REFERENCES categories(cat_id)    
 );

Would you use default NULL here for the cat_id FK or instead have it default to 1 and create a category with that id for unassigned articles? Default 0 obviously won't work because that category doesn't exist and with referential integrity it won't insert the article at all. The other benefit of assigning it default to NULL is we can easily search for all articles that have not been assigned a category yet.

So which would you do here? default NULL seems the reasonable answer but I just want to make sure.

Thanks

[Guimauve]

In my opinion you need a top level parent, which does have an ID to be the root of the hierarchy. NULL should not be used when it implies a value or relationship.

[r937]

definitely use NULL for the parent_id of all categories (there could obviously be more than one) which are at the top level

and definitely allow cat_id to be NULL for articles that you aren't sure which category they belong to yet

definitely, in both cases

[kuato]

Thanks for your responses, guys. I will stick with the default NULL value for cat_id in the articles table.

Actually this is not a real world example but it does apply to something else I'm working on involving teams, divisions, conferences, players, positions, members where a member can signup with a league but possibly not be assigned to a team right away so his/her team_id would default to NULL.

Thanks again.