CODEWITHSUNDEEP
mysql
ghostrider
ghostrider

Reputation: 5259

Insert a new column in SQL

I have a DB consisting of 4 fields.My application will retrieve data from that db. I have one primary key(the id).I also want depending on the id, provide other data that will be organized in a new table. What is better? Create a new table and search again into it, or given the fact that I have already found the row because of the id, create a new element that will be a table. For example can I create a new element named info, and make it be to something like an array,as I want 11 rows,and 2 columns for the info. My SQL code so far is this: 

CREATE TABLE people (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
   name VARCHAR( 100 ) NOT NULL ,
   sex BOOL NOT NULL DEFAULT '1',
   birthyear INT NOT NULL
)

What changes do I need to make? This table is already created.

Upvotes: 0

Views: 235

Answers (3)

Marshall
Marshall

Reputation: 4766

I believe you are hinting at embedding tables. Which isn't really what MySQL is meant to do. Instead, you should do the following; Create a table like that in your example. Then create a new table that will have a column for an ID (which will be the same as that in the people table) and the other various columns. You can then do an inner join to join the two together. Additionally, if you want to reference different tables for different rows, you may want to add in a column for what 'type' it is.

Alternatively, you could use a 'No-SQL' solution like Mongo. This lets you add things dynamically. But I wouldn't suggest doing this until you have a decent grasp of a relational database.

Upvotes: 0

DOK
DOK

Reputation: 32851

I think it would be best to create a separate new table to contain the additional data. That is primarly because you have more than one record per ID in the original table.

The records in the new table would have a foreign key peopleID field linking them to the people table.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86798

If each row in the existing table now also needs associating with an 11x2 set of data, you're best off creating another table.

Don't try to stuff 22 items of data into a single field, it's a really bad idea.


If, however, it's always the same (22 items), you could just add 22 fields. It depends on how that data is going to be used, searched, joined on, etc.


Exactly how to do that depends on your RDBMS and your interface to it. It may be easier to create a whole new table and copy the old data across. Or the environment you have may allow you to add the columns and it do the leg work for you.

Upvotes: 1

Related Questions