INSERT With Indeterminate Number of Columns

[AltarofScience]

Okay, so I need to create a new row in a mysql table, but i only want to inset 1 value and have the rest set to default, which is 0. but, the number of columns in the able is always going to be changing, and the code is going to stay the same. How would i write the INSERT query for that?

[Pikachu2000]

Why would the number of columns always be changing?

[AltarofScience]

Because I'm dumb and I write weird code. Is that question your way of saying I can't do that?

[Pikachu2000]

What I'm saying is that a variable number of columns in a table is typically indicative of poor design, and will likely be a colossal nightmare to work with and maintain. Maybe if you describe what you're doing, a better way can be proposed so you don't end up unwittingly painting yourself into a proverbial corner.

[AltarofScience]

I am making a game. Part of the game is having various buildings built. I set the first column as the id of the colony and the others as the names of the buildings. I could switch it up but either way the numbers of columns will change.

[AltarofScience]

Basically the table is like:

 

 

id name name name name

1    0      0        0        0

2    0      0        0        0

 

 

new prints make a new column and new colonies make a new row.

[Muddy_Funster]

doesn't have to, you can use an another table to link users to buildings:

id  |  user_id  | building_id

1    |    1      |    1

2    |    1      |    2

3    |    2      |    1

4    |    2      |    3

 

then use a building_catalogue table to refference the building id's to relevent details, like name, size cost, number of towns people required before building, time to build resources needed, that sort of thing.

[AltarofScience]

i have a separate table containing the stats of all the buildings. But I need a way to store how many buildings each colony has. The number of buildings possible to make and the number of colonies a player has both change.

[fenway]

If you're dynamically changing the number of columns, you're doing it wrong.

[AltarofScience]

If you're dynamically changing the number of columns, you're doing it wrong.

 

i have been told that.

okay so i need to store the building data of each colony.

new colonies will be created by players.

new buildings will be created by players.

 

explain to me a better way than adding new columns.

[fenway]

Why do you think you need new columns?

[AltarofScience]

because each column represents a building.

so that i can say select * from table where idcol = idcol

 

that way i can call the number of buildings for each building type on a given colony.

 

What I Need To Do:

Colonies can have buildings.

Each building is based on a blueprint.

Players can create new blueprints.

That means I need to add either a row or a column allowing a player to build their new building.

Players can also create new colonies.

That means I need to add either a row or a column allowing a new colony to have data on how many of each type of building are built.

[fenway]

Each one of those "entities" needs to be a separate table.

[AltarofScience]

you want me to make a table for each building and then use colonies as rows?

or a table for each colony and then use buildings as rows?

so many tables arg...

 

what about this?

on colony creation it adds the row to the colony table and then calls the pk value back, and creates a table called that.

so if i had 0 colonies and made a new one it would be idcol 1 as a new row in colonies and 1 as the name of a table.

every time a player added a new print that is a building it would ad a new row to the table. then when i wanted to build something i would write

UPDATE $idcol(built) VALUES($built)

 

one thing though, that would give up to like 10000 tables. is having that many tables a problem?

[AltarofScience]

I came up with a new organization for my tables, which I think is what I will use, since I still haven't gotten a good response:

 

Since a given player is unlikely to make more than 200 colonies, I am going to make tables where columns store the colony id. I will set the rows as buildings since there could be as many as 100k rows in a table, although its very unlikely, and there are likely to be only 100 columns or so.

 

Each player will have their own table.

[awjudd]

Why give each player their own table instead of just having a UserID field within the table that will be used to decide what the user's colonys are and which aren't?

 

~juddster

[AltarofScience]

Why give each player their own table instead of just having a UserID field within the table that will be used to decide what the user's colonys are and which aren't?

 

~juddster

 

Tables have a limited number of columns so if I stored all the data in 1 table I could have like 10000 columns, and that's impossible.

This way tables will have 200 or less columns, most will be like 20-100.

 

The posters kept claiming that having dynamic columns was a bad idea, but then they failed to provide me with an alternate solution so I will have to add columns dynamically anyways.

 

Having a table for each colony, which is what fenway wanted me to do, is impossible because who wants to manage 10000 tables in a database? That is just stupid.

[Pikachu2000]

Google 'database normalization'. You can't force a relational database to behave like a spreadsheet; it's a disaster waiting to happen.

[awjudd]

Maybe we weren't able to provide you with an alternate solution because you never really explained what was actually being stored.  Both a dynamic number of columns as well as a table for each user are very horrible solutions because Pikachu said, you are trying to make your database like a spreadsheet which it is not.

 

~juddster

[AltarofScience]

Maybe we weren't able to provide you with an alternate solution because you never really explained what was actually being stored.  Both a dynamic number of columns as well as a table for each user are very horrible solutions because Pikachu said, you are trying to make your database like a spreadsheet which it is not.

 

~juddster

 

I was very clear on what I need to do.

I need to store a set of buildings for each colony.

 

Now that you say it it sounds sort of like a spreadsheet. Would I be able to integrate a spread sheet into a php/mysql based site? And how would I gather the data from the spreadsheet?

 

Really though I did make a table that works like a spreadsheet. The amount of testing I've done so far hasn't turned up any problems. It works just like I want, but if you think a spreadsheet would work better, how would I do that?

[awjudd]

You don't want your data in a spreadsheet format ... that is just bad and will lead to bad other bad things.  Your database should be relational.

 

I have made a simple structure from that at first glance should accommodate what you are looking to do:

 

Does this help you at all?

 

~juddster

[AltarofScience]

I don't see where you are storing the number of buildings on a colony in there. That structure confuses me, but I can sort of see what you are doing. I don't really understand how relations in databases work. I will go look it up though.

 

Just curious though, why are spreadsheets bad?

[awjudd]

You never actually store the number of buildings in a colony, you will calculate it based on the relationship between a colonies and buildings (i.e. by using COUNT).

 

/* How many buildings are in a colony? */
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
GROUP BY u.Username, c.ColonyName

/* How many colonies does a user have? */
SELECT u.Username, COUNT(c.ColonyID) AS ColonyCount
FROM Users u
JOIN Colonies c ON u.UserId = c.UserId
GROUP BY u.Username

 

Spreadsheets are bad because they are unmaintainable and lead to bigger problems when trying to query the database.  There are many reasons why that you can find on google.

 

With a relational database, you gain so much more than you would get out of the spreadsheet type.  For example, you can now compare two different or all of the different user's colonies in order to get rankings and things. 

 

Sample: http://my.safaribooksonline.com/book/databases/database-design/0201694719/bad-design-hat-not-to-do/230

 

~juddster

[fenway]

Spreadsheets are bad because they're not meant for storing relationships.

[AltarofScience]

Okay, for the blue print part, it looks like you are storing all the blueprints in one table.

I presume the red things are foreign keys?

For the usersblueprints table, how does that store which users have access to which prints?

Is it listing the same user id more than once like:

1 1

1 2

1 5

1 8

2 1

1 9

2 3

2 4

3 6

3 7

And then i say select from bpid where uid= and then the id corresponding to the session user name from the users tables?

 

I set up the colony table by storing the having a username column and then calling all rows from the table where the session username equals the text in the username column. I did the same for the blueprints table. But I am guessing you want me to grab the user id that corresponds to the session username and run the queries with that instead?

 

I think I understand the way the foreign keys interact in your structure, but I am still not clear on how I am gathering the building count.

 

If I have built 20 Basic Iron Mines on Colony A and 35 on Colony B, where Basic Iron Mine is the blueprint name, how does that structure record how many iron mines are on each colony?