3 ms·
While your approach to do this in JSON is cool, I think you have overlooked the 'direct' solution to do this in a RDBMS - with a linked list. Here is a quick su
by jsrn 18y ago
While your approach to do this in JSON is cool, I think you have overlooked the 'direct' solution to do this in a RDBMS - with a linked list. Here is a quick suggestion (works in Postgres):
create table l (
id char primary key references l(prev) deferrable initially deferred,
prev char unique not null references l(id) deferrable initially deferred,
mydata text not null
);
then I populate the table with your example items:
insert into l (id, prev, mydata) values ('A', 'F', 'dA'),
('B', 'A', 'dB'),
('C', 'B', 'dC'),
('D', 'C', 'dD'),
('E', 'D', 'dE'),
('F', 'E', 'dF');
let's see how that looks like:
test=# select * from l;
select * from l;
id | prev | mydata
----+------+--------
A | F | dA
B | A | dB
C | B | dC
D | C | dD
E | D | dE
F | E | dF
(6 rows)
to insert a new item into the list, you would do:
begin;
update l set prev='G' where prev='C';
insert into l (id, prev, mydata) values ('G', 'C', 'data for G');
commit;
so that's one update, one insert for an insertion into the list. Note that the two commands have to be in one transaction, because inside the transaction the foreign key constraint is violated (as allowed by the deferrable initially deferred modifier).
Let's inspect our list again:
test=# select * from l;
select * from l;
id | prev | mydata
----+------+------------
A | F | dA
B | A | dB
C | B | dC
E | D | dE
F | E | dF
D | G | dD
G | C | data for G
so the predecessor of G is C, and the predecessor of D is G, like specified.
Of course, you loose the ability to sort with 'order by', but that's no big deal: you know the predecessor and successor of each item, so it's easy to traverse the list in either order. This could be done on the client side [probably the best solution in your case], in the application code, or inside the database with a stored procedure or with a recursive query (coming in PostgreSQL 8.4), in Oracle it could probably be done with 'connect by'.
In reality, you would of course choose other datatypes for id and prev (probably integer), but I wanted to translate your example as literally as possible. Another problem that's easily solved: how do I get all elements of one list? Solution: Either give me one 'starting element' and the list is traversed and returned. Or introduce a listId attribute and select by that, which is probably faster but without sort order.
- sam_in_nyc 18y agoAha! I did forget the linked list approach. So essentially, each item on the list stores what is before (or) after it. I'm assuming it's an arbitrary choice that you're using "prev" instead of "next," correct? The use of deferred, I've never heard of, but it makes perfect sense in this case. Unless, of course, you want to insert the record first and then modify the update to exclude the item you just inserted. Right now, I'm using MySQL.. I only have 4 tables, and product is not launched. Would you advise switching to Postgres?
- jsrn 18y ago> I'm assuming it's an arbitrary choice that you're using "prev" instead of "next," correct? yes, in effect it's a doubly linked list (circularly doubly linked), so you could remember the id of the first element of the list and then traverse in any order as long as this id does not reappear. > Unless, of course, you want to insert the record first and then modify the update to exclude the item you just inserted. yes - in this case you would get a violation of the unique constraint: begin; insert into l (id, prev, mydata) values ('G', 'C', 'data for G'); update l set prev='G' where prev='C'; commit; this would violate the unique constraint for prev, because after the insert (but before the update!), both the new element and the element not yet updated have prev set to 'C' - the transaction will then be rolled back. In standard SQL this would be possible because it allows to declare unique constraints (and I think other constraints, such as check clauses) as deferrable, too - PostgreSQL doesn't implement this, it allows deferrable only for foreign key constraints. But usually it's no problem to order the commands in a way that only foreign key constraints get violated during a transaction. > Right now, I'm using MySQL.. I only have 4 tables, and product is not launched. Would you advise switching to Postgres? as an entrepreneur, you should probably do what's best for your customers - and they will likely not care which RDBMS you use:-) Perhaps you could play around a little bit with PostgreSQL on the side and (perhaps) make the switch once you are comfortable with it. And keep your JSON-based lists - if the system works, why bother with a rewrite / schema change (for now?). Considering momentum, I think PostgreSQL is gaining steam while MySQL is losing momentum (some key developers left after the aquisition by Sun) - of course, that's my subjective impression. Technically, of course I think that PostgreSQL is better - here is a good comparison: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL for amusement, read the discussion here: http://www.reddit.com/r/programming/comments/764fp/mysql_vs_postgresql/ http://www.reddit.com/r/programming/comments/764fp/mysql_vs_...