Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>In situations like this, where there is a field or a set of fields that simply don't have a value because of the state of the entity that a record represents, I sometimes like to break the table into two separate tables that are related in a 1-to-1 relationship.</p> <p>Let's suppose that your original table is called PurchaseOrder and has a structure that looks something like this:</p> <pre><code>table PurchaseOrder ( PurchaseOrderID int identity not null, -- some other fields that are core to a purchase order -- ... PONumber int not null, -- some other fields that are related to a purchase order when the deal is set -- ... -- define primary key on PuchaseOrderID -- define unique constraint on PONumber field ) </code></pre> <p>My suggestion would be to break that table apart so that it looks something like this:</p> <pre><code>table PurchaseOrder ( PurchaseOrderID int identity not null, -- some other fields that are core to a purchase order -- ... -- define primary key on PuchaseOrderID ) table PurchaseOrderDealInfo ( PurchaseOrderID int not null, PONumber int identity not null, -- some other fields that are related to a purchase order when the deal is set -- ... -- define primary key on PurchaseOrderID -- define foreign key on PurchaseOrderID related to PurcahseOrder.PurchaseOrderID -- define unique constraint on PONumber field ) </code></pre> <p>Now your deal-specific information is in a separate table. Any purchase orders that don't yet have a deal set will simply not have a corresponding record in the PurchaseOrderDealInfo table. Yes, you will need to join the tables together if you want to retrieve all of the information pertaining to a specific purchase order. However, you get some benefits:</p> <ol> <li><p>You can still apply a unique constraint to the PONumber column.</p></li> <li><p>You can take advantage of SQL Server's identity feature to generate your PONumber for you</p></li> <li><p>If you had any other deal-specific columns in your table that you made nullable simply because they wouldn't have a value until a deal was set, then once you move those columns into the PurchaseOrderDealInfo table you can set to not allow nulls (if appropriate).</p></li> <li><p>If you only want to retrieve purchase orders that have deals, then you can simply query the PurcahseOrderDealInfo table without having to specify any filter criteria. Of course, if you also need information from the PurchaseOrder table then you will need to do an inner join.</p></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POHow Can I Generate Random Unqiue Numbers in C#
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDr. Wily's Apprentice
    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. This table or related slice is empty.
    1. COAwesome advice...
      singulars
      1. PO
      1. USpeace
 

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