Database newbie question

[dougo]

I'm just starting to learn about database stuff, playing around with PostgreSQL (and spgsql). So, if I have a data structure that contains an ordered list of another data structure, what's the best way to design the tables? I can think of at least two ways to do it, but I'm not sure I like either: add ordinal numbers to the second table, or store arrays in the first table. In the former, I'd have to renumber if I want to insert something in the middle of a list; in the latter, I'd have to generate a synthetic primary key to use in the arrays. Which I guess isn't so bad, but I don't know how to do this atomically in SQL. Any hints? Is there some other standard idiom for ordered lists? Maybe I need a good online tutorial for database design, any pointers?

Comments

[dougo.livejournal.com]

I understand that rows (and columns) are unsorted, that's why I have to deal with ordinals in the first place. But generating unique IDs is independent of ordering-- I don't care if the IDs are in order, or even consecutive, I just need to guarantee they're unique, even if two INSERTs happen concurrently.

After a little browsing around, I see that PostgreSQL does in fact have this, in the form of CREATE SEQUENCE and sequence functions. Good to know, but I think I don't need it yet.

Anyway, thanks for all your advice.