Databases
StackOverflow2013
Postsdbo
PODB design when data is unknown about an entity?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODB design when data is unknown about an entity?
    primarykey
    data
    text
    <p>I'm wondering if the following DB schema would have repercussions later. Let's say I'm writing a place entity. I'm not certain what properties of <code>place</code> will be stored in the DB. I'm thinking of making two tables: one to hold the required (or common) info, and one to hold additional info.</p> <p><strong>Table 1 - Place</strong></p> <ul> <li>PK PlaceId</li> <li>Name</li> <li>Lat</li> <li>Lng</li> <li>etc... <em>(all the common fields)</em></li> </ul> <p><strong>Table 2 - PlaceData</strong></p> <ul> <li>PK DataId</li> <li>PK FieldName</li> <li>PK FK PlaceId</li> <li>FieldData</li> </ul> <p><strong>Usage Scenario</strong></p> <p>I want certain visitors to have the capability of entering custom fields about a place. For example, a restaurant is a place that <em>may</em> have the following fields: HasParking, HasDriveThru, RequiresReservation, etc... but a car dealer is also a place, and those fields wouldn't make sense for a car dealer.</p> <p>I want to support any type of place, from a single table (well, 2nd table has custom fields), because I don't know the number of <em>types</em> of places that will eventually be added to my site.</p> <p><strong>Overall goal</strong></p> <p>On my asp.net MVC (C#/Razor) site, where I display a <code>place</code>, it will show the attributes, as a unordered list populated by: <code>SELECT * FROM PlaceData WHERE PlaceId = @0</code>.</p> <p>This way, I wouldn't need to show empty field names on the view (or do a <code>string.IsNullOrWhitespace()</code> check for each and every field. Which I would be forced to do if every attribute was a column on the table.</p> <p>I'm assuming this scenario is quite common, but are there better ways to do it? Particularly from a performance perspective? What are the major drawbacks of this schema?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChaddeus
    1. USChaddeus
    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. PODB design when data is unknown about an entity?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODB design when data is unknown about an entity?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODB design when data is unknown about an entity?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMaybe so... but I don't want to build it and then learn of the glaring issues of this design. Thanks for the comment, much appreciated.
      singulars
      1. PODB design when data is unknown about an entity?
      1. USChaddeus
    2. CO@Chad, a quick look at your schema suggests that one `person` can have `multiple`(not fixed number) `data`. If the number of `FieldData` attributes is fixed (for example, one person can only 3 fieldData) then you need to relook into the design.
      singulars
      1. PODB design when data is unknown about an entity?
      1. USDevendra D. Chavan
    3. COYes, the goal is to let visitors add as many attributes about each person in the database as they can, but that the attribute must be unique. For example, if someone already added a website attribute to a person, then nobody else would be able to (unless the just named it something else of course).
      singulars
      1. PODB design when data is unknown about an entity?
      1. USChaddeus
 

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