Re: Real-world DB schema
Posted by: Rick James
Date: January 24, 2009 08:04PM

No schema I can show you. But I can give you my 2-cents worth...

IDs permeate schemas, so I start by discussing them.

ID (or Id or id) is very common, and effectively has a meaning that db folks understand.

student_id, StudentId, studentId, id_student, etc. are variants on ID, and clarify which "id" it is in a multi-table schema. student_id will probably be an INT (or whatever type is desired, such as CHAR(9) with a SSN in it). "student_id" also distinguishes the column from "student_name", which will probably be a VARCHAR.

The "student_id" should be called that in all the tables. But maybe not the "student" table, where it is called simply "id"? This is a style question.

But don't carry the prefix too far. Having all the fields in a table begin with an abbreviation of the table name leads to hard-to-read queries:
SELECT abc_name, abc_foobar, abc_this_that FROM alpha_beta_charlie
Where abc_id = 3;

Lengthy column/table/etc names are handy for the novice, but clumsy once he gets going. "Standard" abbreviations (qty) are often a good idea. But sometimes they may need clarifying -- is that the incoming quantity or the outgoing quantity. So maybe qty_in and qty_out.

Table names are often longer than field names: PurchaseOrders / purchase_orders / ...

When using a table in a JOIN, I like to alias them to a 1-letter or acronym: "FROM PurchaseOrders AS p, JOIN ..." (or "AS po").

camelCase, etc. Different strokes for different folks. EVERYTHING IN CAPS IS HARD TO READ (for me). I prefer: SELECT field_name FROM TableName; Pick your own style and stick to it.

Also, PLEASE format your queries in multiple lines; it makes it easier to read and debug later. One style:
SELECT, b, c
    FROM MyTable m
    LEFT JOIN YourTable y ON
    WHERE m.x IN (...)
      AND m.q = 3
    ORDER BY y.z
    LIMIT 6;

Note that I avoid all caps in the names, but use all caps in the syntax. You might choose to do the opposite.

Project-specific acronyms and abbreviations are OK as long as you don't show the schema to an outsider. He will ask "What is a cbn? Is it unique? Is it a number? etc. If, instead, the field is called "chassis_part_nbr", the reader can guess that it is unique in some contexts (not in others), and is some sort of id (not necessarily a number).

My credentials? This is a compilation of a few thousand schemas and queries used 24/7 by a major internet provider. Then it is biased by my opinions.

Options: ReplyQuote

Written By
January 05, 2009 02:29PM
Re: Real-world DB schema
January 24, 2009 08:04PM
February 02, 2009 08:09PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.