Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This has the 'sql' tag for some reason, so here's how I'd do it in SQL (note there is referential integiry throughout and no <code>NULL</code>able columns):</p> <pre><code>CREATE TABLE Countries ( country_code CHAR(3) NOT NULL UNIQUE ); CREATE TABLE Cities ( city_name VARCHAR(20) NOT NULL, country_code CHAR(3) NOT NULL REFERENCES Countries (country_code), UNIQUE (country_code, city_name) ); CREATE TABLE Users ( username CHAR(8) NOT NULL UNIQUE, country_code CHAR(3) NOT NULL, UNIQUE (country_code, username) ); CREATE TABLE UsersCountries ( username CHAR(8) NOT NULL UNIQUE, country_code CHAR(3) NOT NULL, FOREIGN KEY (country_code, username) REFERENCES Users (country_code, username), city_name VARCHAR(20) NOT NULL, FOREIGN KEY (country_code, city_name) REFERENCES Cities (country_code, city_name) ); </code></pre> <p>Test data:</p> <pre><code>INSERT INTO Countries (country_code) VALUES ('ITL'), ('ESP'); INSERT INTO Cities (city_name, country_code) VALUES ('Roma', 'ITL'), ('Naples', 'ITL'), ('Barcelona', 'ESP'), ('Madrid', 'ESP'); INSERT INTO Users (username, country_code) VALUES ('00000001', 'ESP'), ('00000002', 'ESP'), ('00000003', 'ITL'), ('00000004', 'ITL'); INSERT INTO UsersCountries (username, city_name, country_code) VALUES ('00000002', 'Madrid', 'ESP'), ('00000004', 'Roma', 'ITL'); </code></pre> <p>To be fair, most SQL coders will not have an aversion to using a <code>NULL</code>able column and will prefer all user's details to appear in one table. Assuming your SQL product (correctly) does not treat <code>NULL</code> as a value (for example MS SQL Server does not but MS Access does) then the following will work and is equivalent to the above structure (i.e. again referential integiry throughout despite the existence of <code>NULL</code>able columns):</p> <pre><code>CREATE TABLE Users ( username CHAR(8) NOT NULL UNIQUE, city_name VARCHAR(20), country_code CHAR(3) NOT NULL REFERENCES Countries (country_code), FOREIGN KEY (country_code, city_name) REFERENCES Cities (country_code, city_name) ); INSERT INTO Users (username, city_name, country_code) VALUES ('00000001', NULL, 'ESP'), ('00000002', 'Madrid', 'ESP'), ('00000003', NULL, 'ITL'), ('00000004', 'Roma', 'ITL'); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POBasic database design about intermediate optional models
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USonedaywhen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COonedaywhen, thanks for posting a response. I tagged this as SQL because I thought maybe people adept at SQL could provide good insight on database design, but maybe I falsely assumed that, I'm sorry (I'm new here). In any case, thanks for your response, I was able to read into it a bit, but I must say it's bit too low level and doesn't speak that much to the design question I was going for.
      singulars
      1. PO
      1. USTJ Koblentz
    2. COThe point is, there is no redudancy in these designs! Sure, country_code appears all all three/four tables but each is required to maintain data integrity. You can't 'extrapolate' a country from a city (e.g. is it Paris, Texas or Paris, France?), hence the compound key.
      singulars
      1. PO
      1. USonedaywhen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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