> 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
for amusement, read the discussion here:
http://www.reddit.com/r/programming/comments/764fp/mysql_vs_...