INSERT With Indeterminate Number of Columns

[awjudd]

Yes, there are FKs defined there (on all of the like-named columns).  I wasn't sure if blueprints were one time use or not, but since they do not appear to be from your previous statement, I would make put a primary key on the UsersBluePrints table for the composite key ( UserID, BluePrintID ).  It is this table that maps who has access to which blueprints.

 

Everything you query should be based off of the IDs when possible.  Not only are you going to gain speed, but the auto_increment IDs shouldn't change, but your text fields (i.e. username and stuff may).  As well as if you use a username instead you are wasting space due to data duplication.  If you must go based off of the username, then just simply add a join in onto the Users table in order to restrict it.

 

The proposed structure records how many iron mines are in each colony through the relationships.

 

For example, in order to find out how many iron mines are in each colony you would do something like this:

SELECT u.Username, c.ColonyName, COUNT(b.BuildingID) AS BuildingCount
FROM Users u
JOIN Colonies c ON u.UserId = c.UserId
JOIN Buildings b ON c.ColonyID = b.ColonyID
JOIN BluePrints bp ON b.BluePrintID = bp.BluePrintID
WHERE u.Username = 'foo' AND bp.BluePrintName = 'Iron Mine'
GROUP BY u.Username, c.ColonyName

 

~juddster

[AltarofScience]

Yes, there are FKs defined there (on all of the like-named columns).  I wasn't sure if blueprints were one time use or not, but since they do not appear to be from your previous statement, I would make put a primary key on the UsersBluePrints table for the composite key ( UserID, BluePrintID ).  It is this table that maps who has access to which blueprints.

 

Everything you query should be based off of the IDs when possible.  Not only are you going to gain speed, but the auto_increment IDs shouldn't change, but your text fields (i.e. username and stuff may).  As well as if you use a username instead you are wasting space due to data duplication.  If you must go based off of the username, then just simply add a join in onto the Users table in order to restrict it.

 

The proposed structure records how many iron mines are in each colony through the relationships.

 

For example, in order to find out how many iron mines are in each colony you would do something like this:

SELECT u.Username, c.ColonyName, COUNT(b.BuildingID) AS BuildingCount
FROM Users u
JOIN Colonies c ON u.UserId = c.UserId
JOIN Buildings b ON c.ColonyID = b.ColonyID
JOIN BluePrints bp ON b.BluePrintID = bp.BluePrintID
WHERE u.Username = 'foo' AND bp.BluePrintName = 'Iron Mine'
GROUP BY u.Username, c.ColonyName

 

~juddster

 

I will clarify. Although if I could get a large enough player base I would need to clear inactive accounts overtime, I doubt that will happen, so users are permanent.

Each blueprint is created by a user through the science system. You can only name a blueprint once. Although a blueprint might get 'deleted' or 'archived' if it is deleted and no other player has it it will go to a forgotten knowledge table and other players will be able to buy it. But names are still static.

Colonies are made by players, currently with a link to a page that holds a script since I haven't made colony ships yet. Colonies can be conquered, if the conquering player has a colony on the planet the conquered colonies stats are added to the new colony and that colony ceases to exist. If they don't have one it merely changes colony ownership. Colonies have a default name when created, and can be renamed also.

 

what do the u. c. b. things mean in your code?

and what do you means stores it in relationships? that has me confused. if i have 20 of a building, that 20 has to be recorded somewhere for the query to call.

Maybe I was not clear, each building can be built many times. So if I have 10 different iron mines, I might have something like this:

1 15

2 12

3 4

4 45

5 11

6 32

7 3

8 24

9 0

10 100

 

The only thing I can get from your code is that you think each building can only be built once, like in say, civilization or ogame or something. This is not the case.

[fenway]

You're confused -- there is no such limitation.

[AltarofScience]

no i mean the way he is querying the relational database/designed the database. not that there isn't a structure to do it in sql, just it doesn't seem to be in his examples.

 

where is the number of buildings stored in that table structure? i cannot find it.

 

 

[AltarofScience]

wait do you want to make the building table like this:

bid cid btid bpid name amount

0    0    0    0    bim      1

1    0    0    1    obim    0

0    1    0    0    bim      0

1    1    0    0    obim    1

 

so there are always only 5 columns, but the number of rows increases.

 

so:

colony one has 1 building of type "btid 1  bpid 0" and no buildings of type "btid 1  bpid 1"

colony two has no buildings of type "btid 1  bpid 0" and 1 building of type "btid 1  bpid 1"

 

[fenway]

Yes, the number of rows increases -- the count isn't stored anywhere.

 

I don't understand your table layout, though.

[AltarofScience]

You guys are really confusing.

 

Players are able to build an infinite amount of any single building(theoretically, colonies have a stat called total building slots that rarely goes over 50000.)

There is theoretically an infinite number of different kinds of buildings for each type, and there are about 20 types of buildings.

So for just iron mines I have:

iron mine 1

iron mine 2

iron mine 3

-

-

-

-

-

-

 

then a player can build any number of each of those buildings.

i don't want to call a count of how many different kinds of iron mines i can build. i know how to do that. i want to call a count of how many of each kind of iron mine i have built on a single colony, and this data needs to be stored for every colony. and i also need to store a count of how many of each other type of building i have built. for all 20 building types, for each colony.

 

that table is identical to the table juddster posted, excepting i added a new column and it displays for rows of the table.

the columns are abbreviations of his columns.

building id colony id blueprint id building type id name

[requinix]

i want to call a count of how many of each kind of iron mine i have built on a single colony, and this data needs to be stored for every colony.

No it doesn't need to be stored. You look this up with the COUNT() function whenever you want to know it.

and i also need to store a count of how many of each other type of building i have built.

Again, no. It does not need to be stored.

 

What is your exact table structure right now? For all the tables related to this building issue.

[awjudd]

None of the count / total data need to persisted (i.e. stored in the database as they can all be calculated by a simple query).

 

The sample structure I provided this morning will satisfy exactly that you are asking for if you let it.

 

Aside from not having the 'amount' column which you keep insisting it must have, what can you not do?

 

~juddster

[AltarofScience]

how the hell can you count something that doesn't exist?

i can count the number of rows in a column.

thats what count does.

are you saying you want me to have a row for every single building? and you want the database to store column input for that?

instead of having 10 rows that are the same building, why can't i have 1 row, and then another column with a total value?

do you understand that if each separate building on a colony was stored as a row I would have like 1 million rows?

Is that what you guys keep suggesting?

 

 

[AltarofScience]

None of the count / total data need to persisted (i.e. stored in the database as they can all be calculated by a simple query).

 

The sample structure I provided this morning will satisfy exactly that you are asking for if you let it.

 

Aside from not having the 'amount' column which you keep insisting it must have, what can you not do?

 

~juddster

 

your code requires there to be 1 row for every building in the game. that is millions of rows? How can that possibly be faster than having 1 row for each building print, which stores the total number of buildings built with that print?

 

maybe i just don't understand how computer code works, but my brain will never accept that scanning millions of rows to find all of them that correlate to a specific print is faster than one row with an extra column holding the total.

[awjudd]

Indexes are your best friend.  The database doesn't scan through each and every row.  It attempts to strategically jump between rows based on the key values.

 

~juddster

[AltarofScience]

Indexes are your best friend.  The database doesn't scan through each and every row.  It attempts to strategically jump between rows based on the key values.

 

~juddster

i gather by your statement that i was correct about what you are suggesting in regards to have each individual building be a separate row?

 

you mean it searches the first column for the correct index and then ignores the rest of any row with a different first index? and if its the same first index it checks the next column for the next index, and if its different than the request ignores the rest of the row? and that follows through each key? it still seems like it would take more server resources than just going through ~40 rows and then having an extra column with a number that is updated when new buildings are built.

[awjudd]

Yes, you will have 1 row for every building that the user makes.

 

It may take a small amount more resources but at the end of the day, your database will actually be maintainable.  The extra amount of resources is minimal.

 

~juddster

[AltarofScience]

can you tell me what sort of problems i would have if i just had 1 row for each print and stored the number of buildings on a colony that are that kind in a column would cause? Would it slow down my SELECT queries or something?

 

I guess I will probably just do as you suggest, but my mind is still trying to say no.

[awjudd]

The first issue that comes to mind if you stored the number of a specific building type / blueprint in a column is when there is 1 of the type remaining.  You need to code in extra business logic (either into your PHP code, or as a trigger) in order to actually remove the record (i.e. either do a DELETE rather than an UPDATE, or an UPDATE followed by a DELETE).  Whereas with it broken out, the logic will always be the same.

 

Relational databases are meant to have 1 : many relationships in it.  By collapsing the data so that it does not, not only does it feel wrong on so many levels but you are also masking some of these features.

 

I don't think I explained this well at all ... but yeah ...

 

~juddster

[AltarofScience]

The first issue that comes to mind if you stored the number of a specific building type / blueprint in a column is when there is 1 of the type remaining.  You need to code in extra business logic (either into your PHP code, or as a trigger) in order to actually remove the record (i.e. either do a DELETE rather than an UPDATE, or an UPDATE followed by a DELETE).  Whereas with it broken out, the logic will always be the same.

 

Relational databases are meant to have 1 : many relationships in it.  By collapsing the data so that it does not, not only does it feel wrong on so many levels but you are also masking some of these features.

 

I don't think I explained this well at all ... but yeah ...

 

~juddster

 

yeah i have no idea what you mean. if i destroy a certain number of a building, how would that code with having all rows? would it delete x number of rows where x is the number of buildings destroyed? cause if i saved a value, it would just say, call value, set value to $x, $x=$x-$d, update with $x.

[awjudd]

Until you get down to the list 1 then what would you do?  Keep it at 0?  Kinda pointless.

 

mysql has the LIMIT keyword built-in

DELETE FROM Buildings WHERE UserID = 1 AND ColonyID = 1 AND BuildingTypeID = 1 LIMIT 5

 

That would delete the top 5, there are several other ways you could do the delete as well.

 

~juddster

[AltarofScience]

Until you get down to the list 1 then what would you do?  Keep it at 0?  Kinda pointless.

 

mysql has the LIMIT keyword built-in

DELETE FROM Buildings WHERE UserID = 1 AND ColonyID = 1 AND BuildingTypeID = 1 LIMIT 5

 

That would delete the top 5, there are several other ways you could do the delete as well.

 

~juddster

 

what i am going to do is get the sql file for my database and make a new database with it. then i will alter the current database and try to do it your way, and if i have no problems than i will not need to redo it with the other database.

[awjudd]

It may be a bit of a pain if you have lots of data in your database already to do the migration.  But if you don't then it shouldn't be too bad.

 

~juddster

[AltarofScience]

well since the game is not complete, and its based on a round format, i would need to clean the database out every time a round ends. Since i only have 4 user accounts, i already made all the changes except i had the extra column, so no trouble.

 

thanks for help

[awjudd]

Which column? The amount columns?

 

~juddster

[AltarofScience]

Which column? The amount columns?

 

~juddster

 

yeah, i got rid of the amount column. we will see how it goes.

[fenway]

Good luck -- you'll seriously need it.

[AltarofScience]

Good luck -- you'll seriously need it.

 

why would i need luck if i'm doing what you guys told me to? if i'm doing it right luck isn't necessary.