Databases
StackOverflow2013
Postsdbo
PODatabase schema design
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase schema design
    primarykey
    data
    text
    <p>I'm quite new to database design and have some questions about best practices and would really like to learn. I am designing a database schema, I have a good idea of the requirements and now its a matter of getting it into black and white.</p> <p>In this pseudo-database-layout, I have a table of customers, table of orders and table of products.</p> <p><strong>TBL_PRODUCTS:</strong><BR> ID<BR> Description<BR> Details<BR></p> <p><strong>TBL_CUSTOMER:</strong><BR> ID<BR> Name<BR> Address<BR></p> <p><strong>TBL_ORDER:</strong><BR> ID<BR> TBL_CUSTOMER.ID<BR> prod1<BR> prod2<BR> prod3<BR> etc<BR></p> <p>Each 'order' has only one customer, but can have any number of 'products'.</p> <p>The problem is, in my case, the products for a given order can be any amount (hundreds for a single order) on top of that, each product for an order needs more than just a 'quantity' but can have values that span pages of text for a specific product for a specific order. My question is, how can I store that information?</p> <p>Assuming I can't store a variable length array as single field value, the other option is to have a string that is delimited somehow and split by code in the application. An order could have say 100 products, each product having either only a small int, or 5000 characters or free text (or anything in between), unique only to that order.</p> <p>On top of that, each order must have it's own audit trail as many things can happen to it throughout it's lifetime. An audit trail would contain the usual information - user, time/date, action and can be any length. Would I store an audit trail for a specific order in it's own table (as they could become quite lengthy) created as the order is created?</p> <p>Are there any places where I could learn more about techniques for database design?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMichael Galos
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase schema design
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODatabase schema design
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODatabase schema design
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs there any particular reason to use this 'TBL_' as a prefix? Personally I don't like these prefixes. It make things harder for SQL clients, IDEs code completion feature.
      singulars
      1. PODatabase schema design
      1. USAdeel Ansari
    2. COyeah I wouldn't use TBL_, it was just what I bashed out while typing the question to make sure people knew I meant it was a table name.
      singulars
      1. PODatabase schema design
      1. USMichael Galos
 

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