Managing 10x10 plots of land in a DB

[Callan S.]

I'm thinking of having little 10x10 squares of arable land in a game, maybe 100 of them and thinking on how to store that in a database. I was thinking perhaps a string variable that's over 100 (to account for some extra control variables like who owns the plot of land) characters in length. The the code reads this and converts it to show the plot of land. These would have their own table of entries and so their own index (by the way, when I make a new table, I can never remember the type of index I should set? Primary, Unique or Index? I presume it's the latter, but can't remember from Hall's tutorials)

Is this a fairly okay method?

[Jackolantern]

Are you referring to storing multiple points of data in a single field as a string to be parsed for use? If so, I really can't suggest that, because it makes that data basically unqueryable. Don't worry about having tons of tables, rows, columns, etc. MySQL could handle it with ease

I believe you are referring to a Primary index

[Callan S.]

I imagine MySQL is fine with it - it's me I'm worried about!

I would keep the plots X,Y positions as seperate value in the DB, so they are queryable. But I like to think code wise I can just pull the one (long) string value and know 'okay, THIS is the plot of land - now to read it with some for statements (which I'd have to use either way)'. Rather than calling literally 100 components, either through writing out the DB call string or writing a for loop to do so and...it freaks me out a bit!

But I accept your point - MySQL can handle this and it would be far more accessable to have it all as seperate values in the DB.

[hallsofvallhalla]

Some things do not need to be "queryable" per say.

Like storing A,C,1,2,6,7 into one field instead of 6 fields then spliting it by a comma.

If the letters and numbers stood for different things on the land, like A = grass, C= has water, 1 equals health of plants, and so on, then you will never really have to search for things like that too much and if you did you could still query the one field and break them down. Now in this example it may not be ideal but say your land has 50 attributes. I would use a string over 50 fields.

[Jackolantern]

For me, if I have anything in the db, I like it to be queryable, mostly because you never know what needs may come up in the future, or how the application will progress. I personally would choose the 50 fields over a concatenated single column of 50 comma-separated values. It doesn't really take any extra power to grab those 50 fields versus parsing them in PHP. And if you create some tools to fill-in the data (which I would assume you would, as manually entering 50 values is a pain regardless of whether they are separate fields or 1 string), then the implementation barely even affects the development process.

[Callan S.]

Yeah, but building tools that build tools - it's so recursive it freaks me!

Kind of off topic, but do you think 10x10 is enough to draw a small image? The idea is that not only is it a plot of land, but capable of supporting a small drawing/artwork (in the pixel art style, of course). I guess if I go 15x15 or 20x20, its so big at that point its about the same as going all queryable, so I may as well go all queryable then!

[Jackolantern]

I would probably break off any art-based matrices into their own entity separate from the map entities. I just don't think it would be economical to store them in the database pixel-by-pixel. You would probably be better off creating actual bitmaps and storing them on as such on the server

[Callan S.]

No, the fields of crops are both crops AND an artistic image at the same time. Not just a utalitarian crop - not just a pretty picture.

Sorry I didn't make that clear (it's all sitting in my head nice and clear - why isn't it as clear to everyone else?? )

[Jackolantern]

Oh, I gotcha

[xenoglyph]

I don't see any huge problem with it, depending on what exactly you're doing...it's kind of hard to guess whether or not it's a bad idea without seeing exactly what you're doing. It's common DB design to have the first column be an auto_increment int, set as primary key and used as an id. Which brings something else up. In your example you mention storing owner info in your 100 char omnibus. Don't. That's what the primary key is for. In your user_info table you should have a column (plot_id for example) which is a foreign key that references the primary key of the corresponding plot row. Don't duplicate data. The whole point of using a relational database is for linking groups of data to each other (in this case a user and a plot).

Once you get your db schema all written out you should be able to see changes that need to be made.

edit- bah, necro thread, forgot how slow this place is