Need That Sql Help

[BlackEyes]

So I am making my own timer plugin for cs:s and I need to able to store the basic information on each map.

 

For every map, I need to store:

- An ID that represents the map, possibly just the map name

- 2 main zones (start and end) that each hold 6 coordinates. (each box can be made with 2 points and each point is made of 3 coordinates; so 6 coordinates)

- 2 bonus zones that work just like main zone

- A dynamic amount of anti-cheat zones. Amount of anti-cheat zones changes depending on the map

- A dynamic amount of progress zones that indicate how far the player is. This also depends on the map.

 

I am doing this in SQLite and doing the SQLite in Sourcepawn.

[Papa John]

You can pretty much store everything in the text datatype.

 

For the zones you're going to pretty much have to set up your own container for the coordinates.

 

An Example would be formatting the points like this:

coord,coord,coord;

 

Where the colons ( , ) separate the 3 coordinates that make up the points and semi-colons ( ; ) separate the points. When retrieving the points you simply split the string on the semi-colons and then for each split segment further split the string by the colons to get the individual points.

 

Edit:

 

An alternative method would be creating ID's for the points and assigning them into their own point table. Then in another table where you store the map information simply list the corresponding IDs in a similar fashion.

Edited April 26, 2013 by Papa John

[BlackEyes]

I like the first idea. One important question I have is if I can have a field with multiple values

https://docs.google.com/spreadsheet/ccc?key=0ArC2BLHHEE6KdF9kVEVOTC01ekhta2p5aEdYbURJUUE&usp=sharing

 

Like I mentioned, each map will have different amounts of progress zones and anti-cheats

[Guest The_Monkey]

I like the first idea. One important question I have is if I can have a field with multiple values

https://docs.google....UUE&usp=sharing

 

Like I mentioned, each map will have different amounts of progress zones and anti-cheats

You can have a field with multiple values, but you are better off separating them into separate rows.

[BlackEyes]

You mean separate columns? Because what I was doing was separate rows.

[enigma#]

something like this?

 

Separating the data also helps IMO you organise the information about the coordinates and maps eaiser since you don't have to filter data from a single column to do analysis. (if you wish to do so)

 

CREATE TABLE zone_DB
(
mapID int(8)
mapName varchar(64)
mainStart_x int(8)
mainStart_y int(8)
mainStart_z int(8)
mainEnd_x int(8)
mainEnd_y int(8)
mainEnd_z int(8)
bonusStart_x int(8)
bonusStart_y int(8)
bonusStart_z int(8)
bonusEnd_x int(8)
bonusEnd_y int(8)
bonusEnd_z int(8)
playerProgressStart_x int(8)
playerProgressStart_y int(8)
playerProgressStart_z int(8)
playerProgressEnd_x int(8)
playerProgressEnd_y int(8)
playerProgressEnd_z int(8)
antiCheatStart_x int(8)
antiCheatStart_y int(8)
antiCheatStart_z int(8)
antiCheatEnd_x int(8)
antiCheatEnd_y int(8)
antiCheatEnd_z int(8)
)

 

Addendum:

Depending on how complex you want your info, you can also abstract it further. I don't know exactly what else your plugin will do but that's how I'd frame it.

 

As well, I'm not sure if you really need to create the mapID since mapName can do the same thing and to be fair, it's a bit more user friendly to have the mapName as your filter and/or primary key. (I know SQLite has their own Row stuffs so I'm not sure >.<)

Edited April 26, 2013 by enigma#

[Guest The_Monkey]

You mean separate columns? Because what I was doing was separate rows.

 

Nope, separate rows. Have a common identifier that will allow you to select group of coordinates. Hell, you could even have a column for type of coordinate and store one coordinate per row instead of having soooooooo many damn columns.

[Papa John]

Nope, separate rows. Have a common identifier that will allow you to select group of coordinates. Hell, you could even have a column for type of coordinate and store one coordinate per row instead of having soooooooo many damn columns.

 

You mean like two separate tables?

 

point_Table: id, type(main, bonus, progress, anti-cheat), x, y, z

map_Table: mapName, pointList

[Guest The_Monkey]

You mean like two separate tables?

 

point_Table: id, type(main, bonus, progress, anti-cheat), x, y, z

map_Table: mapName, pointList

 

Close, but one table. I'm assuming he has a table of id's like steam id's. Whatever he uses as his main id (user generated, or steam id) he can use as his foreign key in the table of coordinates.

 

This all depends on the level of data abstraction he wants. Given that this is sqllite we're talking about, he probably doesn't want that much abstraction.

[BlackEyes]

Is this what you were talking about Monkey? I don't see anything wrong with this.

 

https://docs.google....dYbURJUUE#gid=0

 

I have a friend who suggested I make a table for each map. It sounds pretty logical to me.

Edited April 26, 2013 by BlackEyes

[Papa John]

Is this what you were talking about Monkey? I don't see anything wrong with this.

 

https://docs.google....dYbURJUUE#gid=0

 

I have a friend who suggested I make a table for each map. It sounds pretty logical to me.

 

That is a possibility as well but it isn't very efficient.