StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>1- Set two column in my table for two value. 2- Create another table for 2nd value.</p> </blockquote> <p>It depends on your actual data. First of all, you should get a basic understanding of <a href="http://www3.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html" rel="nofollow">database design principles</a> and <a href="http://databases.about.com/od/specificproducts/a/normalization.htm" rel="nofollow">normalization</a>. Normalization is primarily to save space and help ensuring data consistency.</p> <p>For your concrete issue: You say that you have two <code>values</code> and that one record can either store one or two of them. Lets assume the value is a primitive type like <code>INT</code>. In this case, I would go with one table having two columns:</p> <pre><code>CREATE TABLE dataTable ( first INT NOT NULL, second INT ); </code></pre> <p>This table can store one number (<code>first</code>) and an optional <code>second</code> number. I omitted a primary key which, in reality, you should add.</p> <p>Now, you say that you want to store <code>addresses</code>. An <code>address</code> is not a primitive type, since it is usually composed of first name, last name, street, zip code, city, and depending on your location some additional information.</p> <p>Again, the straight forward way would be to simply create one table with two sets of these data:</p> <pre><code>CREATE TABLE dataTable ( firstName VARCHAR(40), lastName VARCHAR(40), street VARCHAR(40), zipCode INT, firstName2 VARCHAR(40), lastName2 VARCHAR(40), street2 VARCHAR(40), zipCode2 INT ); </code></pre> <p>However, this approach is unflexible and might also waste space. In this case, I would separate ("normalize") the schema and use two tables:</p> <pre><code>CREATE TABLE address ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, firstName VARCHAR(40), lastName VARCHAR(40), street VARCHAR(40), zipCode INT ); CREATE TABLE dataTable ( first INT NOT NULL, second INT, FOREIGN KEY (first) REFERENCES address(id), FOREIGN KEY (second) REFERENCES address(id) ); </code></pre> <p>With this approach, you can store ("reference") either one or two addresses in your data table. The drawback is, of course, that you need to insert data into two tables when creating a new record, and that you need to properly join the tables in your queries. However, it allows you for example to add additional constraints, e.g. to define that a first name is optional, but a last name is mandatory ("NOT NULL"). This would not be possible with the first approach, since you could not distinguish between the whole (second) address being optional or only the first name being optional.</p> <blockquote> <p>notice they can't store more than two address,It's limit.</p> </blockquote> <p>Well - yes, until your customer comes back and needs to store three :-)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload