So it last secret is certainly one checking you to definitely a NodeId normally participate in a love because the a parent

We have the two tables lower than, ideas on how to impose constaint you to definitely Nodes which have NodeTypeId 2 (CannotBeParent) can’t be a grandfather in the place of cause?

Modified because of the gao.seng Wednesday,

Responses

I would start with incorporating a flag to dining table [NodeTypes], to suggest in the event your node kind of is also engage just like the mother or father into the a love.

Note that I am also incorporating a separate restriction by (NodeTypeId, CanBeParent). It is redundant, nonetheless it will allow me to later explore a foreign secret pointing to it.

The next step is to try to incorporate that it line in order to desk [Nodes], to make certain the couples (NodeTypeId, CanBeParent) is available inside table [NodeTypes]. We’ll also add a calculated continuous column (ParentNodeId_CanBeParent), that have really worth equal 1, to make [ParentNodeId] to become listed on because mother automagically, and you can resource desk [Nodes] using the few (ParentNodeId, ParentNodeId_CanBePArent).

It could be much easier in the event that were able to use a select report inside a check limitation, or being capable create assertions (view constraint at the database top), however, T-SQL lacks of these keeps as of today.

6:58 PM

All the responses

So where are Alex Kuznetsov when you need him? Alex came with constraintss in order to demand organization rules really people carry out merely consider was you are able to that have causes.

Then again, he often reaches one adding an auxillary line. Perhaps you deliver some more records on which you colombiancupid inloggen are seeking to get to? Which could supply determination to some services.

In the event the Is available (Select Ideal step one step one Of NodeTypes Internal Sign-up Nodes Toward Nodes.NodeId = And you can Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you may NodeTypes.NodeType = ‘CanBeParent’) Initiate Put = 1 Stop More Start Place = 0 End Get back End;

, NodeType varchar(255) maybe not null ) perform desk Nodes (NodeId int constraint PK__Nodes_NodeId Primary Secret , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId records NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId sources Nodes (NodeId) Consider (dbo.CanBeParent(FK_ParentNodeid) = 1) )

insert Nodes values (1,1,1) <-- this will pass, because it has a parent 1 insert Nodes values (2,2,1) <-- this will pass, because it has a parent 1 which can be a parent insert Nodes values (3,2,2) <-- this will fail, the parent nodeId=2, is of NodeTypeId 2, which cant be a parent

Advised since answer because of the sdsuser Wednesday,

We realize Alex a great deal into the SQLBlog I will look greater there, however you is actually proper the greater I do believe about it request new nearer I get so you’re able to an answer. It is brand of particularly that have a consumer with an allocated conversion agent while having the people in the staff chain from order each other along, although customers is more including an online staff member however, can’t would people. In this situation a workstation can also be chain together a couple of gizmos off a residential district pond such as for instance adult cams and microphones, nevertheless workstation alone cannot be a grandfather tool. In my opinion the customer-sales person table framework get apply right here, I will review the software construction document to find out if one is reasonable.

Recommended once the answer by the sdsuser Wednesday,
Unproposed because the answer from the sdsuser Wednesday,

Significantly less undetectable given that a cause, this may performs. I’m able to observe nHibernate wants they the next day, and you will my personal director.

Modified by the Kalman Toth Tuesday,

I might begin by adding a banner to help you desk [NodeTypes], to point in case your node type of is engage as moms and dad from inside the a love.

Note that I am also including a different constraint of the (NodeTypeId, CanBeParent). This is redundant, it enable us to later on have fun with a foreign key leading to help you it.

The next step is always to include this line also to dining table [Nodes], to ensure the few (NodeTypeId, CanBeParent) exists during the table [NodeTypes]. We shall include a calculated continuing line (ParentNodeId_CanBeParent), which have well worth equivalent step one, to make [ParentNodeId] to become listed on since the father or mother by default, and you can reference table [Nodes] using the pair (ParentNodeId, ParentNodeId_CanBePArent).

It could be convenient in the event that were able to fool around with a select report into the a constraint, or being in a position to perform assertions (have a look at restriction at the databases height), however, T-SQL does not have of these possess to date.

6:58 PM

There is no such thing as a “node_type_id” there can be a “_type” or a “_id” in a correct data model but not a weird hybrid. An identifier is for entities (emp_id) a type encoding a scalar value (blood_type).

I avoid using meta data instance “pk_” or “fk_” during the data feature term. The goal of a data element name is to tell us the goals by the nature and not how it is being used in this particular dining table.

When you use more-size of string like this, your own dining table tend to fill which have garbage. A programmers fool around with records if gang of beliefs are large otherwise volition. For example, your order outline UPC you will resource new List desk to help you enforce a guideline that individuals just take requests to possess merchandise into the stock.

If the place are small and steady, up coming i use a check (x During the (..)) constants. Instance, “sex_password TINYINT Standard 0 Perhaps not NULL See (sex_password For the (0, step 1, dos, 9)) — iso-5218

Would Dining table Nodes (node_id INTEGER Maybe not NULL Primary Secret, node_variety of INTEGER Maybe not NULL Records Node_Sizes (node_type), parent_node_id INTEGER References Nodes (node_id));

Everything have created was an adjacency record design. They mimics non-relational pointer chains within the SQL. It contains one another agencies (nodes) additionally the hierarchy (relationship). But a correct can’t be one another (Chen? E-R modeling? Freshman database group at school?). Therefore while this is still an invalid studies design, you have authored it

Do Dining table Forest_and_Nodes (node_id INTEGER Maybe not NULL Top Secret, node_type of CHAR(1) Standard ‘N’ Maybe not NULL View (node_input (‘P’, ‘N’), — P= It is possible to Mother or father, N= Non-Mother moms and dad_node_id INTEGER Records Tree_and_Nodes (node_id), Examine (Situation Whenever node_variety of = ‘N’ And you can mother_node_id Is not NULL Up coming ‘F’ Else ‘T’ Prevent = ‘T’) );

–CELKO– Guides for the Celko Collection to own Morgan-Kaufmann Posting: Analytics and OLAP from inside the SQL / Research and you will Databases: Concepts used Study / Proportions and you may Conditions inside the SQL SQL having Sming Design / SQL Puzzles and you will Answers / Considering inside Set / Woods and you will Hierarchies into the SQL

Share on Facebook