SQL Question

[RichAlgeni]

I have to store lines of narrative in a table. There can be anywhere from 5 to 500 lines of narrative, with each line up to 110 characters per line. Each line will also have a corresponding date and time field (column). Has anyone done anything like this? Can you suggest how it would be best formatted in the table schema?

I will be using the PostgreSQL database with the native PB calls.

Thanks!

[citystate]

I'd suggest three columns - line_number, line_content, date_time
so probably something like:
"CREATE TABLE narrative(line_number AUTOINCREMENT, line_content TEXT, date_time INTEGER);"

[spikey]

Assuming that each line of narrative pertains to some "thing" you'll probably want a field for the parent object ID too...

[RichAlgeni]

Ah, so you're saying make the narrative a separate table???

I think the clouds are starting to part!

[spikey]

A little off topic but I always have a look at http://www.databaseanswers.org before creating a new database. Follow the link to "Data Models". There are several hundred sample schema diagrams available on the site created by an expert on the subject (he lectures at the University of Cambridge). You can often find something very close to what you're planning - which can save time and/or gotchas...

For example you could (maybe) use the "Call Centers" schema - but ditch all the extraneous stuff about Solutions, Contracts etc... or the "Loan Management System" schema - rename "Payments" as "Annotations" and ignore "Payment Types".

[RichAlgeni]

Thanks so much folks!

[Zach]

I happened to stumble across this thread.


I did visit the link to that site, but I had trouble really getting anything useful out of the Data Models, I think because I don't really understand the power of various databases and how they work / what they are actually capable of..

I think its the whole "Relational" aspect that I want to know more about.. If anyone knows some good simple reading, links would be awesome.

Generally when I think of using a database, I'll make a table and then I will create a basic Row/Column schema to store certain data with. What am I missing out on beyond that? I've heard of terms such as "linking tables" and having Primary Foreign Keys (a key that is a primary key in another table?)

I guess I'm wondering what it is that I am missing out on, and if I might actually find it useful to my game.

I've always been more concerned about the "Visual" of a collection of data, and not the particulars to how it is stored.. I guess to me, it makes no difference if I store information about a house, and I use a single Table for the house, and then use a "Room" as a Column, instead of breaking things up and having a table for each room, etc..

[spikey]

10 Useful articles about database design:-
http://woork.blogspot.com/2008/09/10-us ... abase.html

10 things not to do:-
http://www.simple-talk.com/sql/database ... -mistakes/