Bulletin board - Database optimisation

Part I

Revised 09 Dec 10 01:00 EST

Looked at your DDL. Ok. We need to take a step back and organise your database first. That will solve half your problems (your SQL will be straight-forward; and fast; less indices; no temp tables required). For a while I thought, aha, you have your columns, it must be stable, but there is no chance. Top down from scratch, ok. Have a look at this Entity Relation Diagram (no use working on the Data Model, which is Entities, Relations and Attributes, until we get the ERs right), and check that it is correct.

  • The way to do that is, answer the following questions (short answers are fine). These questions are clarifying the Entities and Business Rules. How you understand databases in general, and your data in particular is crucial. You have come a long way, on your own, so we can take it from there.

  • I think ▶this post◀ might be helpful to you, in order to understand the formal stages that should be followed; which we are short-circuiting here.

  • Most important, totally, and completely, forget about the function and any coding requirements. Data has to be modelled independent of the application, simply as Data. Function Modelling is a different science. First get one right; then get the other right; and the two together play beautiful tunes. Try jamming them together; doing both tasks at the same time, and they won't even make a suburban garage band.

For brevity, and the sake of anyone reading this, I with use a Closed and Open Section; when an Open item (discussion) is closed, I will make it concise, and move it to the Closed section. Maintain the numbering, because things sometimes come back to haunt us. You may wish to do the same, or even delete the discussion on your side.

The links for the pretty pictures are at the end.

Apologies: the editing does not work; sub-numbering is inconsistent

Closed Issues

  1. users.bb_locations_csv is a many-to-many relation between users and locations:
    • Each of those elements should be an entry in a discrete column, in a discrete row
    • One users can have many locations and 1 location can have many users is many-to-many
    • Read ▶this post◀ for a discussion of how that is treated and what stage it is dealt with
    • At this Logical Stage, that is just a n::n relation, as I have drawn, you can forget about it for now, it will be supplied, simply, when we get to the physical Stage.
    • Trust me, I will provide code that in no more complex than ...WHERE IN () for your declared purpose.
    • On second thought, if I break your fingers, you will type even slower, so I better not
    • Ok, your app is browser based, and the page is dynamic (my advice was for static pages that need to be touched up); go ahead with check boxes.
      .
  2. users.bb_categories_csv is many-to-many relation between users and categories
    • Ditto.
      .

  3. Confirmed: a bulletin (bbs) does not exist without an user; an user issues a bulletin, and that starts the whole cycle; then invites replies and ratings.

    3.1 Confirmed: There is really only one bulletin board and it does not exist as a Thing in the database.

    3.2 Confirmed: that the org will never have more than one bulletin board, and the classifications and categorisations are all adequately handled by the Category table/function

  4. Deleted.

  5. Confirmed: The difference between bulletins and replies is that replies are dependent on a bulletin to exist, they do not have a title and they are not categorised by location or category because they are dependent on the bulletin itself to exist.

  6. Deleted.

  7. Comments noted. Resolved.

7.1. For each single bulletin submitted by another user, each user can post more than one reply.

7.2. For each single bulletin submitted by an user, that user can post one, or more than one reply.

7.3. Deleted.

7.4. Deleted.

The Data Model now allows more than one reply per user per bulletin; including the User who submitted the bulletin.

.
8. Confirmed: each user can post at most one rating to a bulletin (which can be revoked/changed)
.
9. Confirmed: each user can post at most one rating to a reply (ditto)

10.1. Given: username comes from the organisation and is the unique name that identifies employees. For example emails are [email protected] - authentication is done with ldap and this is required in order to connect an retrieve other information about the employees

  • Confirmed: UserName is an excellent Identifier

10.2. Confirmed: FirstName, LastName ... BirthPlace, etc remain as (the traditional) columns for ensuring People are not duplicated.
.
11. Given: At the moment we can Identify our offices by casual names which are generally know within the organisation, since we only have about 3 main offices and many field offices. So examples would be Washington DC or virginia field office. In total I think we will try and keep the total below 20. I want to record the exact address of each location as well because that could be used to uniquely identify offices to users.

  • Provided: StateCode+Town as PK; IsMainOffice as boolean.

.
12. Confirmed: Description and Name for Category are required.
.
13. Given:Users will not be able to post to some categories. Only users with sufficiently high rights will have the right to post to certain categories.

  • Provided: Permission in User, Location, Category is a method of evaluating such rights.

.
14. Confirmed: Location.Administrator is UserId of admin for the Location.
.
15. Given: There will only ever be a need for a like or a dislike. I don't think there needs to be a neutral position because this is the same as just not voting? Liking seems more relevant to bulletin replies that posts to be honest. Ie 'i see your response and instead of writing my own I will just agree with you - the existing bulletin board is somewhat of a social aspect in the orgainsation and I think liking and disliking/agreeing and dissagreeing creates a level of controversy that encourages participation. However liking or disliking a bulletin may not always be entirely appropriate.

15.1 Provided: Like as boolean in BulletinRating and ResponseRating. This will require interpretation on every access.
15.2. When it is no longer a boolean, it can be changed to a RatingCode, and implemented as a Lookup table. The names are then determined by Joins, and interpretation is eliminated. I drew this in the First Data Model, so that you could see what I meant 15.3. Removed in the Second Data Model.
.
16. Confirmed: each user has a home Location (other than the list of Locations that they are interested in).
.
17. Confirmed: Permission as per (13).
.
18. Confirmed: Further Permissions may be be required, as per Data Model.

18.1. If you do this now, you won't have to worry about when organisation decides to prevent a certain Person from posting Responses or Bulletins, or Rating them; and wants that feature implemented yesterday.

18.2. Even if you do not implement it, leave gaps between the values you do implement.
.
19 Confirmed: a Bulletin is about a Location.

19.1. Confirmed: There are no Bulletins without a Location

19.2. Confirmed: There are no Bulletins without a Location.

19.3 Confirmed: There are no Bulletins without a User (declarative). But so far we have no way of constraining that User; therefore any User can inset a Bulletin for any Location ( you could constrain it in code, eg. to Locations each User Is Interested In.

19.4 Confirmed: There are no BulletinRatings without a Bulletin and a rating User.

19.5 Confirmed: There are no Responses without a Bulletin.

19.4 Confirmed: There are no ResponseRatings without a Response and a rating User.

19.7. But, there can be Users, Locations, andCategories`, independently.

.
20. If you do not mind, I will provide naming conventions, etc. They should be self explanatory, and the value will show up only when you start coding SQL. Please ask, if anything isn't. For starters, all names are singular. Mixed Case is easier to read (you are supposed to use capitals for SQL language).

20.1. My experience is table_name as opposed to tableName are really technie forms, and users do not like them; Consistent mixed case is liked by everyone. It is one of those things that is impossible to change, so choose carefully.
.
21. For your need to group tables together, which is good, keep in mind that that is a Physical issue. At the Logical Data Model level, the tables have normal names, uncluttereded by physical issues. Imagine that the physical tables are prefixed with something like (and please use capitals for this):
- REF_ for reference (such as User) and lookup tables
- BUL_ for Bulletin system
.
I am not able to name tables with uppercase letters? Im not sure why. I don't know why I can't have uppercase table names. Is it to do with using MyIsam database tables?

The universal convention is that SQL Language is expressed in upper case; every report and admin tool I have ever used generates such SQL code. So we can't use upper case. Lower case or mixed case only. So the choices boil down to table_name or TableName; we need a separator of some kind. For reasons already provided, I strongly recommend mixed case, capiatlised, and not the OO style with the leading letter uncapitalised.

.
22. rank (all) can be derived directly from the database (remember, do not worry about the code during Data Modelling). If you store it, it is a Normalisation error; a duplicated column; which has to be kept up-to-date; which can get out of synch with the derived value; which is called an Update Anomaly. Fifth Normal Form eliminates Update Anomalies. That is my minimum level of Normalisation, so that is what you will get from me.

22.1. I am not interfering with the sort order or popularity issue at all; in fact, by the sounds of it, you haven't closed that functionality. I am only taking redundant data, the rank column, out, as part of the Normalisation process.

22.2. Here's a ▶Quick Tutorial◀ on the RANK() operator (as it is commonly known). It is not ANSI SQL; it is an Oracle and MS extension. However it is not required if you understand Subqueries, which is why Sybase does not have it. I doubt MySQL has it, so you need to get your head around it. Understanding Scalar Subqueries is a pre-requisite. Sybase syntax, so whack your semi-colons in, etc. Feel free to ask specific questions.
.
I have never seen that approach of writing Rank = (SELECT.... Is that the same as (SELECT ...) as Rank?

I have posted a separate Answer for that.

.
22.3. Needing to understand why, is no problem at all. Only children blindly follow simple rules, and you are certainly not one of them.
.
23. Confirmed: users.total_bulletins is redundant; it can be derived. Removed.
.
24. All your PKs are Ids. Haven't you gotten tired of getting lost in the code yet ? Forget about sticking Idiot PKs on everything that moves, let's find out How your users Identify their Entities; what Entities are truly Independent, and the other which depend on Independent Entities.

24.1. Never use Id or any such form. Where it is a PK, use the full form.

24.2. Call location_id, location_id, wherever it is, including the PK table. The exception is when you need to show the role. This will become clear in the Data Model.
.
25. You have no Declarative Referential Integrity, no Defined Foreign keys. That is bad news for many different reasons. Once these questions are clairified, please add them in. DRI means that as much as possible, if not all, Integrity is Declared in SQL. ISO/IEC/ANSI SQL standard allows for this, but the freeware end of the market does not provide the standard, and is slowly catching up. It means the server will not allow a row in the FK table to be added unless the PK exists in the parent table. MySQL recently provided DRI for Foreign Keys. For FKs, refer to ▶this article◀.

25.1. For CHECK constraints and RULES, you will have to implement those in code.

my foreign keys are like, users-id(fk) = users.id(pk) Im not sure how to add them other that what I have done but will certainly do so once I know how to.

That's not adding them into your db; that's merely referencing columns in a WHERE clause in Data Manipulation Language, not Data Definition Languge. Adding them, so that they function at the db/server level, means declaring them in DDL, as per the linked article. Then MySQL will stop a row from being inserted to a child table (FK) where the parent PK does not exist. That is Referential Integrity. If it is declared in DDL, it is Declarative Referential Integrity.

In addition to enforcement of RI, everyone can see the definition: report tools can be used by the users to access and report from the db, without having to get someone to code a report.

Yes, as far as I know. Confirmed at ▶this site◀. The code I have provided for the subquery uses DRI, so we can test that and get it out of the road early. You have to check for your specific version of MySQL.

Twenty-Five. Comments Noted. I ama not a MySQL specialist. Yes, those are the issues you have to figure out for yourself. In general, from my perusing, MySQL is legless; for anything SQL-ish, you need InnoDB.

But do not let that hold you back. Use Engine=MySQL for now, without the Declarative SQL, and keep going with both the Data Model and the Subquery. Work on InnoDB in the background.

To be clear, the DDL I have provided should work for MyISAM (and "do nothing" in the DRI department, until you get InnoDB).

.
27. Given: I have rethought the sorting requirements for bulletin. Users could sort chronologically- easy,makes sense. Users could sort bulletins by the date of the latest reply to the bulletin. Then we can forget about rank and it should be really easy to sort bulletins chronologially by the time of their last response? What are your thoughts.

Yes. that is sensible and quite common, most people understand chronological order. You will have to mess with the filters they choose in the search window (choose: Location or list; choose: Category or list; choose: My Bulletins or all).

Open Issues

(Nil)

Data Model

Ok, assuming you do not have issues with the ERD, and implementing all Closed Issues, I have modelled the data, and prepared a Fifth Data Model 09 Dec 10 for your review. I definitely need much more feedback, questions, etc, on this. I am experiencing difficulty accepting that it is done. Probably best to start writing real code for your problem areas.

Links

▶Link to IDEF1X Notation◀ You really need to read and understand this, before you read the Data Model.

▶Link to Fifth Bulletin Data Model◀ The Entity Relation Diagram is on the first page, followed by the Data Model.

  • The Keys are pretty much straight IDEF1X (except for UserId which I provided as a counterpoint); which means purse Relational Keys. Un-enhanced and not optimised for Physical considerations. Before you baulk at them, first notice them, register them, and evaluate them. Of course we can add Idiot keys, but before we do that, let's make sure we understand what we are going to lose.

  • Notice the Identifiers (solid lines) as per the Notation document. The spine, the vertebrae of the system is Location ... Bulletin ... Response.

  • Notice that Keys actually implement many Business Rules.

  • Notice the Natural Hierarchy that I have rendered. See if there is any meaning in it for you.

  • The VerbPhrases are really important; see if they mean anything.

Comments re First Data Model and Responses

One question I have is that the primary key of the location will be used to form the child primary key?(they are joined by a solid line) I don't really understand that concept

Yes. the PK for Location (above the line) is (StateCode, Town). That PK the two columns together, a compound key, is migrated from Location to Bulletin anyway, as an FK (bold). We are additionally using it to form the Bulletin PK (above the line).

If and when we need a Surrogate key, we will add it. For now, we are working out the Identifiers. So the question to contemplate is:

  • What is a good Identifier for Bulletin ?, what do your users naturally use to Identify a Bulletin ...
  • "have you seen the bulletin from Virginia FO yesterday ?",
  • "Sally from Washington sure writes good bulletins", etc.

or why that relationship does not exist between the user and the bulletin?

Well, that relation cannot exist between User and Bulletin, but a relation exists, the dotted line, meaning UserId is an FK in Bulletin(bold), but not used it to form its PK (below the line).

Or do you mean: the User is a strong Identifier for Bulletin (and therefore should be used to form the BulletinPK, therefore the line should be solid) ?

Fine. Excellent. That is what modelling re Identifiers is all about. That clears up an area that I did not like, in that we had non-unique indices. That resolves my issue as well.

  • As per intention stated further above, since I have now shown Rating as a table and what the rendering would be, once, I shall remove it

  • I think Permission should be an Entity.

  • Bulletin PK is now (StateCode, Town, UserId, SequenceNo). To be clear, SequenceNo is within StateCode, Town, UserId: it will be 5 for Sally's 5th bulletin re MO/Billngs FO.

  • Note that user Settings BulletinsPerPage,etc, are 1::1 with User, so they are in User; child table would be incorrect.

  • Typographical errors corrected.

Comments re Second Data Model and Responses

  • The PKs for both Bulletin and Response have been changed to reflect (7). BulletinNo and ResponseNo have been replaced with BulletinDate and ResponseDate (which used to be CreatedDate), in order to allow multiple replies per User per Bulletin.

Comments re Third Data Model and Responses

Trust you had a good break.

  1. At least 30 years ago (that I am aware of), the giants in the industry had this debate. Names are always singular. Tables are nouns. VerbPhrases are verbs. This is not limited to db naming conventions, it applies to documents, theses, dissertations, etc. You may have 5 conclusions at the end of the the doc, but the section or chapter title, in both the ToC and the top of the page is "Conclusion".

    After fighting them all the way through Uni, as soon as I started my first paid programming job, and saw the importance of the rules in the real world, as opposed to the theoretical arguments we had in college, I gave it up as a waste of time. All that time and energy I wasted was released to do productive work. Since then, I don't question the giants; I just accept. That their minds are greater than mine. It is like accepting Standards, or behaving within the law, or God. I have no really, really good reasons for doing anything illegal.

    Anyway, the ease of languaging (discussion, SQL, documentation) that is supported by such rules cannot be adequately explained; as you write more and more SQL code, it will become clear.

    You are always free to use whatever you want. I deliver singular only.

  2. Fine with me.

    But you need to keep in mind, those two elements, in the identified sequence (ala non-PK Unique Index, or Alternate Key) are universally required to establish Uniqueness for a Person. Removing them will result in two things. First, you will no longer be able to identify uniqueness across Users (and thus you may have duplicate rows). Second, the AK becomes non-unique, an Inversion Entry.

  3. The point is (contrary to one of the posts), any column that is 1::1 with theUserPK, should reside inUser. All preference settings. Since we cleaned up theInterestedLocationsandInterestedCategories, I know only of onlyBulletinsPerPageremaining; but I am sure there are others. IsPreference2is an eg. of a boolean;NumPreference3is an eg. of an Integer. Etc. You can tell me what the real Preferences are.

    (Let's try that in plural: ... any column that is 1::1 with theUsersPK, should reside inUsers. Just doesn't do it for me, I get hung up on the broken English, and I am a bit precious about my mother tongue.)

    Data Model Updated.

  4. Excellent. Let me know when you are comfortable with that, and I will give you the Physical Model.

    How about the VerbPhrases ?

Comments re 06 Dec 10 20:38 EST (Small Updates)

.
28. Where there is only one occurrence of PK as an FK, of course, the FK column name is the same as the PK column name. However, when there is more than one occ of the FK (take a look at ResponseRating), there are three UserIds), we need to differentiate them. In IDEF1X terminology this is called Roles. The Role of the User who issued the Bulletin is Issuer, and so on. Obviously it is better to use that name, and keep it consistent throughout the hierarchy (not UserId in Bulletin and then when we get to Response, where there are two, and a differentiation is demanded, change it to IssuerId. I thought you might have a problem with that; in the early stages, the usage is Issuer.UserId so that it is absolutely clear the it is UserId as an FK, and the Role is Issuer; when we get to the physical model, it gets simplified to IssuerId.

Likewise, we have many DateTime columns (Date for short if you like; otherwise Dtm), that need to be differentiated.
.
29. Did the IDEF1X Notation doc not make sense ?

  • The PK for each table is above the line, in the specified order.
  • Remember we are carrying the PKs of the parent tables anyway, and if there is meaning, using those FKs to form the child PK.

  • For Bulletin:

    • The Location FK (StateCode, Town) for which it is Issued
    • The UserId of the Issuer
    • and DateTime it was Issued, to make it unique.
    • therefore (StateCode, Town, IssuerId, BulletinDate)`

  • To delete all ResponseRatings for this Bulletin, use WHERE = on those four Bulletin columns.

.
30. Because (State, Town) is the PK of Location, carrying wherever. And it forms part of the Bulletin PK, so any dependent tables carry those columns because they are carrying the Bulletin PK.

Look for the coloured Tabs (This version only)
.
32. Those are Verb Phrases. The way to read them is detailed in the Notation doc. It appears you have a good handle on it. It is really important to get the table names (and the Verb Phrases) right, because change is difficult after implementation. If you tell me Office is better than Location, that's fine with me.

Read: Office Is Activated By Bulletin

Feel free to supply another Verb Phrase.
AFAIC, the Office is dead to the rest of the org, and only comes alive on their radar (is activated by) the issue of a Bulletin.
I realise it sounds silly here, but ignore that for a moment, something along the lines of "Office expresses its aliveness; advertises its activity, by issuing a Bulletin".

Have a quiz at Mark's Sensor Data Model, for some nice Verb Phrases.

We had previously identified that (State, Town) is the PK, I will leave that as is Refer to (38) for change.

.
33. Worth discussion. Yes, if you are going to display it when (eg) displaying Responses, and the users understand UserName. No, if it is 30 bytes, and there is also an unique 4 byte UserId. The idea is to make these choices consciously, aware of what you are giving up, when you eventually decide that some 6 column 30-byte key is too cumbersome to migrate to the children.

  • I did state at the outset, I would use UserId as a typical Id Pk, because it is carried/migrated to several child tables.
  • We can leave how that is created for later. But it is a pure Surrogate PK.

.
34. No problem. Category already has it. I'll change Order to ListOrder.

.
35. Sure. Based on what I have read and heard, I am quite happy with it. But I would like more back-and-forth to achieve some confidence, before you write code. Alternately, view it as a learning experience, and accept that the model and code may change later. Would you like me to produce the Physical now ? If you give me any and all corrections, I will publish the next version. I am expecting preferences in User. Also, quickly run through the functions and check that you have all the columns you need.

Do look at some of the other answers, for the purpose of learning, and interest.

.
36. Joins. You just join on four three columns as opposed to one. SQL is cumbersome with joins, and the new syntax which was supposed to make it easier, is actually more cumbersome. My coders never write joins: we save time and typos. I have a proc that given two or more tables, will generate the code with all the columns and joins. I don't know enough of MySQL to convert that for you.

Data Model Updated.
.

Comments re 08 Dec 10 20:49, Fourth Data Model and Responses

.
Check the previous section immediately above, there are small updates.

IDEF1X: Your speed is fine.

Note the child always "inherits" the Parent PK, as an FK (either solid or broken line), otherwise there is no Relation between them. By using these columns that exist in the child anyway, to form the child PK, we carry the meaning (and that is the difference between solid and broken). And thus we do not need to look for an independent Identifier for the child. The Relational power in this method will become clear later, when you are coding.

The section we are dealing with is about Identifiers: natural vs unatural; meaningful vs meaningless. Later you will see how we can use the Relational capability of the engine, when the child PK is formed from the parent PK. (Isn't your surname the same as your father's ?)

It is also important to understand Relational databases and their capability. That is lost when we approach the database (eg) from an OO perspective, and treat it as a location to make our classes "persistent". Therefore, we will try to learn and use Relational terms. It gets difficult when you go to France and expect that they speak American, and use the same currency; learn to speak 10 words of French, and they welcome you with open arms, and you'll have quite a different experience with the locals.

Anyway, go ahead with implementing the model. Just realise we will probably make a change at some point. Save all your DDL. Save all your test data as insert statements or as a table backup or character format export (no idea what MySQL can/cannot do in this area). .
37.1. Handled, the n::n Relation with Office & Category. You will only "see" that when we get to the Physical Model.

37.2. Done.

37.3 Done.
.
38. Excellent. Shorter as well. Note they will never be able to have two Offices in the same Zip Code. NUMERIC(5,0) is good, but I thought the US was moving towards 7 digits. Doesn't matter, you can figure it out; it is an excellent PK for Office. Now this column, which was part of Address, probably ZipCode, has been elevated to a higher purpose, without duplication; since we are carrying it in 5 child tables, and we want the PK name to be clear, as per previously explained conventions, we will call it OfficeCode; OfficeZipCode might be silly.

We need an Unique Index on Name to ensure they do not add two Offices with the same name. Note, for explanation purposes, this is is actually the logical key of Office, replacing (StateCode, Town), and it remains so.

I still think you may need StateCode and Town as a quick reference (other than sitting somewhere in Address)

Data Model updated, Fifth now available for review. You did not state your preference, for ...Date vs ...Dtm. I am going with the latter, as it is more spceific, identifying the time component as well. Easy to change.

This Answer has reached maximum length. Continued in "Part II"


They key to having an efficient database is to simplify. The main goal of a relational database is not to repeat any information. I took your SQL dump and quickly drafted a simpler version that is normalized, to the best of my knowledge. I did leave some of the fields you had in for cvs's ect. I have removed fields that it would be simpler to just recalculate by querying the db when the information is needed, such as a users total posts and a ranking of a given post. I also removed your bb_replies as you can accomplish the same result with referencing to a parent post. I have renamed the tables slightly to what made sense to me, you can use what ever naming scheme you feel comfortable with. I find that using terms that are simple makes it easier to understand how the data relates to each other.

I must admit that I do agree with some of the comments above, there are plenty of BBs out there that work just fine and would have all the functionality you are looking for. And you are lucky I am in the reading mood tonight lol that was one long question. Simplification is key in everything :)

alt text

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';


-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(45) NULL ,
  `password` VARCHAR(100) NULL ,
  `email` VARCHAR(255) NULL ,
  `first_name` VARCHAR(100) NULL ,
  `last_name` VARCHAR(100) NULL ,
  `permission` INT NULL ,
  `created` DATETIME NULL ,
  `modified` DATETIME NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `categories`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `categories` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `description` TEXT NULL ,
  `order` INT NULL ,
  `admin` INT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `locations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `locations` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `description` TEXT NULL ,
  `address` TEXT NULL ,
  `order` INT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `posts`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `posts` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `post_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `category_id` INT NOT NULL ,
  `location_id` INT NOT NULL ,
  `title` VARCHAR(45) NULL ,
  `content` TEXT NULL ,
  `created` DATETIME NULL ,
  `modified` DATETIME NULL ,
  PRIMARY KEY (`id`, `post_id`, `user_id`, `category_id`, `location_id`) ,
  INDEX `fk_posts_users` (`user_id` ASC) ,
  INDEX `fk_posts_posts1` (`post_id` ASC) ,
  INDEX `fk_posts_categories1` (`category_id` ASC) ,
  INDEX `fk_posts_locations1` (`location_id` ASC) ,
  CONSTRAINT `fk_posts_users`
    FOREIGN KEY (`user_id` )
    REFERENCES `users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_posts_posts1`
    FOREIGN KEY (`post_id` )
    REFERENCES `posts` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_posts_categories1`
    FOREIGN KEY (`category_id` )
    REFERENCES `categories` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_posts_locations1`
    FOREIGN KEY (`location_id` )
    REFERENCES `locations` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `likes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `likes` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `post_id` INT NOT NULL ,
  `like` TINYINT(1)  NULL ,
  PRIMARY KEY (`id`, `user_id`, `post_id`) ,
  INDEX `fk_posts_users_users1` (`user_id` ASC) ,
  INDEX `fk_posts_users_posts1` (`post_id` ASC) ,
  CONSTRAINT `fk_posts_users_users1`
    FOREIGN KEY (`user_id` )
    REFERENCES `users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_posts_users_posts1`
    FOREIGN KEY (`post_id` )
    REFERENCES `posts` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sort_options`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `sort_options` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  `description` TEXT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `preferences`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `preferences` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `pagination` INT NULL ,
  `sort_option_id` INT NOT NULL ,
  `categories_csv` VARCHAR(45) NULL ,
  `locations_csv` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`, `user_id`, `sort_option_id`) ,
  INDEX `fk_preferences_users1` (`user_id` ASC) ,
  INDEX `fk_preferences_sort_options1` (`sort_option_id` ASC) ,
  CONSTRAINT `fk_preferences_users1`
    FOREIGN KEY (`user_id` )
    REFERENCES `users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_preferences_sort_options1`
    FOREIGN KEY (`sort_option_id` )
    REFERENCES `sort_options` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Subquery First, then the RANK() Function

Relax, son, we'll get there! Your speed is fine.

Preparation

The first thing, you really need to get access to a decent set of manuals, for your specific flavour of MySQL. I found ▶this one◀. As before, you have to do your own debugging, but I am now providing SQL that is as close to generic MySQL as possible. I've confirmed that everything we are going to be doing is entirely possible in that flavour of MySQL (I don't know what flavour/version yours is, except ENGINE=MyISAM).

Subquery

Ok, let's start again. I have written a ▶series of SELECTS◀, to lead your through the process. Please complete each one, and understand it completely before progressing to the next. If you have any questions, stop, and post the question.

The code is written and tested in Sybase; then downgraded for MySQL (from perusing the web, eg. the above site), and tested as much as possible in that state.

The first bit creates and loads three tables for use.

  1. The first SELECT is a straight join of the three tables, no subquery. You need to get that to work; that is, understand what is does, fix any syntax problems; figure out the differences between the SQL I provide and the SQL runs on your server. And get used to making those changes. We can't keep stopping for that.

  2. The second SELECT produces exactly the same result set. It introduces the concept of a Subquery, which is used to populate a single column.

Drive that bus. Respond when you're done or if your have problems.

Responses to Your Comments of 03 Dec 10 17:51

  1. Straight Join
    I have never seen that way of doing joins before, I have always used left join, right join or inner join. Ok so for this first query we are just joining the two tables student and course with the studentcourse table sitting in the middle as the associative table. Results are repeated as expected because one student might be on more that one course and they will have a result for that course.

Yes.

That ( x=y in the WHERE clause ) is the traditional way of identifying joins, it is much more clear; the LEFT/RIGHT/INNER/OUTER JOIN syntax is the "new" way. Much more cumbersome AFAIC, but the learning is relevant because it is fundamental to what comes later. Feel free to convert to the latter syntax, and back again, for purposes of understanding.

Repeats ? That is not what repeats or duplicates mean. All the rows are discrete, true rows in CS. You should get the same 15 rows in every report (as we progress).

(ps when i direclty create the tables using queries you provided, the names are converted to all lowercase while the column names can still be camel case.)

MySQL is very strange. (It appears to be doing the naming conventions for us!)

.
2. Simple scalar query
A few issues with query. You use the alias(in the scalar subquery) before you have defined what it is?(StudentCourse sc) I guess I always incorrectly assumed that you have to say define an alias before you use it.

You are thinking procedurally. SQL is a set-oriented language, for manipulating Relational sets of data.

The whole query gets evaluated and optimised in one pass. There is no "before" or "after". I am defining it in the same batch of SQL that I am using it.

I don't entirely understand the use of the alias 'in-ner' in the scalar subquery, is this to say that you want it to check each row individually(not sure how to explain this) instead of on a table wide check?Ie when you are doing this check make it local to the particular row you are on?(terrible explanation sorry).

For purposes of understanding/debugging, evaluate the subquery first (the contents of the brackets), alone. Understand it fully. Note the use of "sc" and keep it in your hat.

  • in_ner and sc are ALIASES, that is, handles for the table name that it sits next to in the FROM clause; that we use elsewhere in the code for convenience
  • in_ner is a descriptive name for the table referenced in the Inner Query, the Subquery
  • sc is a descriptive name for the table referenced in the Outer Query, which is only Outer because it has an Inner query, otherwise it would be a flat query
  • we could just as easy use fred and sally
  • Aliases such as in_ner and out_er are meaningful when the same table is referenced in both the Inner and Outer queries.
  • notice the join between the Inner query and the Outer query WHERE in_ner.CourseId = sc.CourseId
  • I have related the table referenced in the in_nerquery to the table sc referenced in Out_er query
  • Such a subquery is called a Correlated Subquery

See if you can visualise the Outer query (result set) as a grid, a spreadsheet, 15 rows by 4 columns.

  • Make sure you understand that Outer query, "easy" as it is. Notice that it is the same as (1. Straight Join), with a different method of populating one column.

As i understand it the scalar subquery asks for Name where the courseId's in Course and studentcourse are the same.(pretty straight forward) and is an alternative to saying that in the where,

Yes, exactly.

And notice that we are after only the Course.Name which is a 1::1 join from StudentCourse to Course, on CourseId. Notice exactly the WHERE clause in (1) that we are replacing in (2); in (1) it applies to all rows.

But because we are grabbing one datum; one cell; one item for a specific row/column; not all rows; not all columns, it is called a Scalar.

We are obtaining it using a subquery, which has to be constrained to the specific row. Therefore we need to relate the row from the outer query to the row in the Inner query.

  • so the Correlation between the Inner Subquery and Outer (specific row) is required.

  • And if we did not have that identification of the specific row, we would be loading rubbish into the Scalar, or it would return a Table (not a Scalar value) and the query would fail.

    • Try that, take the WHERE CourseId = sc.CourseId out
    • So that you know what the error message is, so that when it happens in future, you will know "Aha, I am returning a table, not a scalar; I am missing something in my Inner WHERE clause; I am not identifying a specific Correlated row".
      .
  • it is not quite "asks for Name where the courseId's in Course and studentcourse are the same"; it is getting the Course.Name for a specific StudentCourse.CourseId, which is identified from the outside, whatever sc row it is.

with the differnece that you can make this check row by row before the where.

you are thinking procedurally; there is no "row-by-row"; the dbms is set-oriented; the result set you are building is a set. Re-state the question is set terminology.

I used Course instead or in-ner, what is the point of using an alias in this case, is it just to show that aliases can be used?

Yes. And to highlight issues. And to differentiate the Inner Query from the Outer query. In the Inner query, the "inner" Alias, or any alias is not demanded. Only the Alias relating to the outer query is demanded.

Something I don't understand here is that when I try to do this, 'course.Name' it says unknown Course.Name in field list. this is the way that I have always defined that i mean Name in the Course table and not some other table. What would happen if I had two tables with a name column?

Exactly. If it were ambiguous, then you would have to supply the table name or alias; where it is not ambiguous, it is not demanded, but nice to have for documentary, clarity, purposes. You have to figure out why MySQL is not accepting it. Mixed case/lower case madness ?

I have also never seen that order by syntax, I can see that 1 and 4 mean the column numbers but why bother passing it two columns?

Huh ? Because I want the result set ordered by Course.Name in ascending order, and within that, by StudentCourse.Mark in Descending order.

If I did not state the order, MySql would produce the result set in whatever order it gets it from StudentCourse (chronological ?; by index ?). Whatever that default order is, find that out, you need to know it, and thus avoid an ORDER BY, when it is unnecessary.

  • Take the ORDER BY out and play with it.

  • Try ORDER BY 4 DESC, 1

It is not "passing", I am telling it what to do with my result set, in the one SQL command. The only passing you are doing is between your app (PHP ?) and MySQL.

2.1. Ok, when you finished with (2), and completely happy that you understand it, do this exercise.

SELECT  (SELECT Name
            FROM  Course
            WHERE CourseId = sc.CourseId
            ) AS CourseName,
        () AS FirstName,
        () AS LastName,
        Mark
    FROM StudentCourse sc
    ORDER BY 1, 4 DESC

  • Produce the same grid format, we want the exact same result set as (1) and (2).

  • Fill in the two pairs of empty brackets with the appropriate subquery; ie. write a subquery to populate the FirstName column, and another to populate the LastName column

Responses to your Comments re Third Data Model

2.1. Perfect, yes, we move on.
.
You are cooking with gas, so if you don't mind, I will take your text, and annotate it a bit; notice the differences, they may or may not be subtle.

The correlated scalar subquery says that for each course id we need the highst mark, as opposed to the highest mark for all the courses. This is where the correlated aspect of this subquery comes into play because we are relating the outer query to the inner query for this particular row. [Yes!] The way that I am currently visualizing [That's it, use the visual part of your mind, not the serial part] it is that the outer query runs through the tables putting together the result table set, and each time it creates a row it runs the scalar subquery and picks out [a single value to fill the cell; here it is] the highest mark where the courseId's match, so when it is on a row where the course id is 66 then the scalar subquery is only looking for the max mark where the courseId is 66.

I could hardly have said it better myself.

There is no such thing as "result table".

Add one more definitive item.

  • The outer query defines the result set.

    • The subquery is independent of that; it is merely Correlated or Indexed.

Ok, so you have that SQL working, right ?

Now that you understand that, the next step is to visualise the result set, and to visulaise the subquery (3, unchanged) filling the entire column. if the above text was a balloon filling one cell at a time, then visualise hundreds of ballons, filling consecutive cells. Then visualise a bucket poured into the column.

Now leave that two dimensional result set alone for a minute, and visualise another layer on top of it. This is the parallel layer, where you write your subquery code.

If ever you have difficulty getting a subquery to work, go back to this, your way of visualising, one result set, and another layer for the subquery, which pours a bucket of scalars in, to fill the column. It eliminates all the well-known subquery coding bugs; removes the use of GROUP BY, DISTINCT, and all those ham-fisted methods of getting a long angry snake to fit into a jam jar.
.
Three more small steps before you proceed to (4).

2.2 Re-read my response (2) above, all the way down to this point. No skimming. This is because when you teach your mind something new and different, you need to re-inforce it. It is an officially recognised and labelled technique.

Responses to Comments of 08 Dec 10 20:49

2.3. Write that query (3) without using subqueries, and ensure you check the results. If you catch yourself laughing when you are writing the code, it is a good sign. As long as you produce the correct result set, you pass, but try to write the most efficient code (fewest COUNTS and GROUP BYs, etc). Do this only if you want to run circles around your peers, to be able to answer any "how do I code ..." question on your database.

I'm not sure what you mean by write that query without using sub-queries? I thought we wanted to avoid the use of group by's etc

Yes. Absolutely. You've walked forward. Now walk backward without tripping. This will really help your understanding of walking forwards, when it is better to use a subquery vs a join. Code the query with GROUP BYs and COUNTs. The fewest. Don't laugh.

2.4. Write the subquery (3) on your database, to produce a list of Bulletins, the outer query has to be FROM bbs only; with a count of likes, and a count of dislikes. So trunacte the tables and do 10 or 12 meaningful INSERTS, fibe minutes, big deal.

I used the method of using sub-queries on my database to put together a list of bulletins replies, count the number of reply likes and dislikes and get a particular users rating. it was great because I didn't have to use any group by's or counts and I didn't have to create temporary tables like I did for the bulletins.

Well, that's perfect. Now we are getting a bit of Relational Power in your spinach.

Now, go and look at this question and answer; ensure you compare the code. You've come a long way in just a few days.

When you finish (2.3), read your (2.4) query again, to refresh yourself, and move onto (4).

If you get stuck, replace the word "Rank" with "CountOfStudsWithHigherMark", and give it another go.

Responses to Comments of 11 Dec 10 13:14

2.3 I am having trouble writing that query without a scalar subquery. Scalar subqueries always made more logical sense to me even before I knew how to do them. That is why I said "I guess the problem I am running into here is, how do you refer to user-id = x in this particular row, not in all the row" in that previous question. Correlating the scalar subquery to the main query with and alias was the answer.

The (2.3) exercise is intended for you to:

  • really understand the incorrectness of the fat query with the GROUP BY (in a relational database using a set-processing relational engine) vs the correctness, elegance, and speed of the Correlated Subquery. You have achieved that. That will place you above your peers, in terms of SQL coding ability.

  • be able to identify when a fat WHERE clause and when a Correlated Subquery is appropriate. I am not sure, but it looks like you have achieved that.

  • be able to correct and debug this kind of issue when maintaining code written by others, and to be able to teach them the distinction. It sounds like you have a good visual, relational ability; which has been re-inforced by the exercise; and now you cannot go back to inferior methods. That is, you can understand and fix incorrect SQL code, but you cannot communicate that to others.

As long as you understand those distinctions and accept that, I am happy to drop (2.3) and move on.

Read your (2.4) query again, to refresh yourself, and move onto (4).

If you get stuck, replace the word "Rank" with "NumStudentsWithHigherMark", and give it another go.

Don't read further. The following is "old code"

Here's a ▶Quick Tutorial◀ on the RANK() operator (as it is commonly known). It is not ANSI SQL; it is an Oracle and MS extension. However it is not required if you understand Subqueries, which is why Sybase does not have it. I doubt MySQL has it, so you need to get your head around it. Understanding Scalar Subqueries is a pre-requisite. Sybase syntax, so whack your semi-colons in, etc. Feel free to ask specific questions.

I have never seen that approach of writing Rank = (SELECT.... Is that the same as (SELECT ...) as Rank?

Yes, () AS Rank instead of Rank = () are both legal SQL; MySQL may not like the latter form. The brackets containing the Subquery, of course. Note that Rank is the name of the derived column.

I have already stated that understanding subqueries is prerequisite. That means that millions before you have had this problem, and the lecturers figured out that you would suffer less frustration if you followed the lessons in the prescribed order. So forget RANK for now, and learn subqueries.

Try this (I supply ANSI Standard SQL; I do not have MySQL; you will have to syntax-fix it for MySQL; I don't fix syntax problems; that's your job):

SELECT  COUNT(*)+1 AS Id_iot  -- not you, everyone who uses them blindly
        (SELECT title in_ner FROM bb_locations WHERE out_er.bb_locations_id = in_ner.id) AS Location,
        title AS Bulletin,
        created_date AS Date
    FROM bbs out_er

  • in_ner and out_er are ALIASES, that is, handles for the table name that it sits next to in the FROM clause; that we use elsewhere in the code for convenience
  • in_ner is a descriptive name for the table referenced in the Inner Query, the Subquery
  • out_er is a descriptive name for the table referenced in the Outer Query, which is only Outer because it has an Inner query, otherwise it would be a flat query
  • we could just as easy use fred and sally
  • notice the join
  • I have related the table referenced in the in_nerquery to the table referenced in out_erquery
  • Such a subquery is called a Correlated Subquery
  • This is just an example, simple, so that you can learn Subqueries; purposely chosen to provide the same result set as one you are familiar with producing, using straight joins (bbs and bb_locations in the FROM clause, joining via the WHERE clause or JOIN syntax).
  • Because it produces a single value, it is called a Scalar Subquery (those that produce rows are Table Subqueries; and cannot be used like this, to load a single value into each row)
  • There is no suggestion that anyone should "use Subqueries instead of Joins". Absurd. Subqueries have their place, and Joins have theirs. Misue is a different thing.

Now, drive that bus. And don't talk to me about RANK until you can drive that bus around every corner in your database neighbourhood without killing any children.

I don't understand inner and outer, when I google them I get INNER JOIN what are they called so I can research further

Aliases. Refer above.

When I run that select statement I get this error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE inner.Mark >= outer.Mark ) FROM studentmark outer ORDER B' at line 5

  • first, as per reasons detailed above, I can't write MySQL syntax, and debugging is your job
  • second, I realiise that you can't debug what you can't understand, so drop it for now (it has to do with RANK) and as you learn the MySQL flavour of SQL, all these things will be resolved
  • third, let me assure you that it runs on any Standard SQL server. It gets used in about 10 courses a year, so hundreds of participants per year. I just ran it again on Sybase, just to check.
  • first thing I would suggest is, since the MySQL optimiser sucks dead bears; it does not understand context, inner and outer are probably being treated as reserved words. So change that as per the above code.

Tags:

Mysql

Php

Database Design

Normalization

Recent Posts