Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your design looks good to me. I always prefer to have a couple of extra joins on the design step over spending time reorganizing data after system went into production. You never know in advance what kind of reports will be requested by management/sales/financial people, and proper relational design will give you more freedom.</p> <p>Also, you cannot blame only a couple of extra <code>JOIN</code>s for your performance issues. You should always look at:</p> <ul> <li>data volumes (and physical data layout),</li> <li>transaction amount and density,</li> <li>I/O, CPU, memory usage,</li> <li>your RDBMS configuration,</li> <li>SQL queries quality.</li> </ul> <p>In my view, <code>JOIN</code>s will be on the bottom of this list.</p> <p>As to the <a href="http://www.postgresql.org/docs/current/static/ddl-constraints.html" rel="nofollow">RI constraints</a> (Referential Integrity), I've seen a couple of projects that had been running without any Primary/Foreign keys for increased performance. The main excuse was: we have all checks embedded into the <em>Application</em> and <em>Application</em> is the only source of any changes in the system. On the other hand, they agreed, that it is not known, whether systems were in a consistent state (in fact, analysis showed they were not).</p> <p>I always stick to creating all possible keys/constraints on the design state, as there always will be some “cowboys” around, who will dig into your database and “adjust” data they seem fits better. Still, you might want to temporarily disable or even drop some constraints/indexes for the bulk data manipulations, which is also an <a href="http://www.postgresql.org/docs/current/static/populate.html" rel="nofollow">official recommendation</a>.</p> <p>If uncertain, create 2 test databases, one with and another without constraints. Load some data and compare query performance. I think it will be similar.</p> <p>And here my comments on your sketches, decisions are all yours.</p> <ul> <li>You might want to create a common <code>contacts</code> table the same way you did for <code>addresses</code>, i.e. add <code>contact_id</code>, <code>owner_contact_id</code>, etc. columns to the target relations instead of referencing relations from <code>contacts</code> table;</li> <li>As you have only one column in <code>contacttype</code> table (and in case you'll have a common <code>contacts</code>), it's better to move the only field away and avoid this table;</li> <li>You seem to have mixture of singular/plural names for your tables, better to stick to a common pattern here. I personally prefer singular;</li> <li>In <code>pharmacygroup</code> your PK is named <code>id</code>, while all the rest PKs follow <code>table</code>id pattern, it will be easier to write scripts later if you'll use a common pattern here;</li> <li>In <code>addresses</code> table you have fields with underscores, like <code>street_name</code>, while elsewhere you avoid <code>_</code> — consider making it common;</li> <li><p>References are named differently. Although it is not so highly important, I do have a couple of systems where I have to rely on the constraints' names, so it's better to use some pattern here. I use the following one:</p> <ol> <li>prefix <code>p_</code>, <code>f_</code>, <code>c_</code>, <code>t_</code>, <code>u_</code> or <code>i_</code> for primary, foreign keys, check constraints, triggers, unique and other indexes;</li> <li>name of the table;</li> <li>name of the column constraint/index/trigger refers to.</li> </ol></li> </ul> <p>Why I prefer naming tables in singular form? Because I always name PK using <code>table</code>_id pattern, and IMHO <code>pharmacy_id</code> looks better then <code>pharmacies_id</code>. I use this approach as I have a bunch of general-purpose scripts which relies on this pattern when performing data consistency checks prior to loading it into the main tables.</p> <p><strong>EDIT:</strong> More on contacts. You can use <code>contact_id</code> in all your tables, making it a <em>primary contact</em>, whatever this might mean in your application. Should you need more contacts to be there for some relations, then you can go with different prefixes, like <code>owner_contact_id</code>, <code>sales_contact_id</code>, etc.</p> <p>In case you expect a huge number of contacts to be there for some relations, like <code>pharmacygroup</code>, then you will can add an extra table like this:</p> <pre><code>CREATE TABLE pharmacygroupcontact ( contactid int4, groupid int4, contact_desc text ); </code></pre> <p>It partially copies your initial <code>groupcontacts</code>, but consists of two FKs and a description. Which approach is better I cannot tell as I'm not aware how Application is designed.</p>
    singulars
    1. This table or related slice is empty.
    1. POdb design critic /suggestions
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USvyegorov
    1. USvyegorov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POdb design critic /suggestions
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthnks for the detailed answer..i would definitely look into all the suggestions esp. with respect to naming conventions, however right now, I am more interested in the design part. For the common contacts table, are you suggesting that I add a different contact_id to the pharmacy and the pharmacygroup table for every different type?? So for type owner, there would be owner_contact_id, for type accounts, it would be account_contact_id etc..How would this take into account, if a new type was introduced down the line?
      singulars
      1. PO
      1. USAngela
 

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