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

[petdance.livejournal.com]

Basically yes, you need ordinal numbers to say what order they should go in.

Any of Joe Celko's books are good for reading up on SQL stuff, except for "Instant SQL Programming" which he disclaimed this summer when I heard him speak. (oh, and don't hear him speak)

Whyncha post some tables that we can see?