How to create properly tree-hierarchy

database-design

I'm using web2py to create an application

I've got 2 different roles: client and referents. Every client can have multiple referents.
Both client and referent can insert row in a table, called object.

The user table, where client and referent are registered, has a column called "client_id".
Every referent in the column_id has the id of the client to whom he reference to.

The problem is: which value should a client have in client_id?
A possible answer could be NULL or the id of the client itself.

Which one is better? Or there's something better?

Best Answer

Main reason why I'd choose NULL is a bit simpler queries - you don't need to care about self-relationship when you are querying ,for instance, number of referred users by the given. You can also save some space in case you create an index on client_id (it usually makes sense) because some RDMS, for example Oracle, don't include NULLs into index, others, like SQL server, allow filtered indexes (WHERE client_id IS NOT NULL).

Related Solutions

The best database design for this situation

A rule of thumb I use, is that it's bad database design to have to alter a table to accomodate a code change or a new feature if it can be planned for in the future.

That said, I would use a variant of Option 1

Approvals
    Id PK
    ApprovalTypeID FK
    Status


ApprovalComments
    ID PK
    ApprovalId FK
    Comment

ApprovalTypes
    ID PK
    Name

Now when adding new types of objects that need approval, you only need to insert a row into ApprovalTypes instead of altering a table to add a column.

Sql-server – Storing revisions in SQL Server (simply much like TFS)

Obviously when you check in a changeset, you know who the customer is (or was) according to what you describe about your process. Even TFS requires something to be the same, so that it can maintain history.

In any case, in multiple versions of the same object, what is constant?

Is the "Name" constant? (Probably not.) If the Customers table were that simple I do not see what you are tracking other than a name change. But likely the table is much more complex.

But as long as you know this is the same Customer, you should carry a constant value in the new Customer rows. Something like:

 Table: Customers
 Columns: Id | CustomerNumber| Name

That way "Id" is just a row surrogate key, but CustomerNumber would be assigned once and would be a datum included in the versions of the data you are tracking.

I suppose that the CustomerNumber could be the first Id assigned to this Customer, but I would suggest an independent numbering scheme, perhaps check-digited for a degree of protection from entry errors, should that ever be needed publicly.

Best Answer

Related Solutions

The best database design for this situation

Sql-server – Storing revisions in SQL Server (simply much like TFS)

Related Question