I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc
extension but here is another problem.
Any user can refer only 3 other users at max.
So for example I have this relation of users:
CREATE TABLE users
(
id serial NOT NULL,
referred_by integer NOT NULL,
created_at time without time zone NOT NULL,
updated_at time without time zone NOT NULL,
CONSTRAINT primary_key PRIMARY KEY (id),
CONSTRAINT referred_foreign FOREIGN KEY (referred_by)
REFERENCES users (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
I can use connectby
function to query any hierarchy. But when it comes to inserting related models I have to check if a user has not already referred 3 other users before inserting any new user under him. If he already have 3 users then it need to cascade down the tree and put it as a leaf node of tree which is further a heavy query to run on larger hierarchies.
For cascading new users to leaf node Is it possible to determine the nearest node where new user can be put ? for example If this is the data in table:
____________ A ___________
/ | \
__ B __ __ C __ ____ D ____
/ | \ / | \ / | \
E F G H I J K L
And A refers one more user, Now, since A
's downline is fully saturated any new user under him should cascade down the tree, and there are 9 places where the new user can be put.
E F G H I J K L D
out of which the nearest node is D, so incoming node should go under D instead of E,F..K,L
I have 3 questions:
- Most important, Is it possible to restrict the number of records under each user ? I dont want to rely on triggers to check for number of records and cascade down if required. I can change the design if necessary.
- How can I 'efficiently' fetch the nearest leaf node of any tree after determining that the root node is saturated ? By efficiently I mean that the same query might need to run a couple hundred times per minute and the data set has no depth limit. Again, I am open for design suggestions if any.
- Is it possible to create partial index for
referred_foreign
so that the first user who will not be referred by anyone can still satisfy the foreign integrity ?
Is there any better design or something that I am missing ? (I know I am missing a lot just dont know what it is)
ltree
? If not, I'd probably be looking at recursive PL/PgSQL functions, or maybe a recursive writable CTE. You'll have to think very hard about locking - in particular, you'll need toSELECT ... FOR UPDATE
each parent node before inserting a child, to ensure you don't get two concurrent inserts. – Craig Ringer Feb 5 '14 at 11:10ltree
module but I think I overlooked some of its functionality or I dont completely understand how I can use it in conjugation with other records in database. and of course I need to think about locking, thanks for confirming that I was not over concerned about it. Can you help with someltree
implementations ? – Gufran Feb 5 '14 at 11:27