Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>I suspect I may also need a PermissionsInRoles junction table as well as one for PermissionsInUsers?</p> </blockquote> <p>Well, you already said one of the requirements was "Each Role has a fixed set of Permissions". So to fulfill that requirement, you need to store permissions that apply to each role.</p> <pre><code>Table: role_permissions PK: (Role, Permission) Role Permission -- User Create User Update Admin Create Admin Update Admin Delete </code></pre> <p>You don't need <em>two</em> different tables to implement that requirement. </p> <p>By the same token, you've already said "Each User can have additional Permissions not provided by their Roles". To fulfill that requirement, you have to store user-specific permissions.</p> <pre><code>Table: user_permissions PK: (username, permission) username permission -- user1 Rename user1 Leak to News of the World user2 Randomly corrupt data </code></pre> <p>So, again, you don't need <em>two</em> different tables to implement that requirement. Both those tables are in 5NF.</p> <blockquote> <p>But this is just ridiculous isn't it?</p> </blockquote> <p>What's ridiculous? </p> <ul> <li>That you have very elaborate requirements for permissions? </li> <li>That you store business data (like permissions) in tables? </li> <li>That it takes more than one table to model your permission requirements? </li> <li>Something else?</li> </ul> <p>If you want specific advice about your actual tables, edit your question and insert DDL for your tables.</p> <hr> <p><strong>Later</strong></p> <p>I looked at your diagram. Not every table needs an id number; id numbers have nothing to do with normalization. </p> <p>If I were designing your system, I probably wouldn't use id numbers in the tables Roles, Permissions, and UserTypes until I saw a performance problem that id numbers could fix. (In most systems over the last 30 years, that means, well, almost never.) Before I used an id number, I'd also consider and test using a human-readable code instead. Human-readable codes often don't require joins; id numbers always require joins.</p> <p>In most SQL dbms, you can combine a data type and check constraint in a <code>CREATE DOMAIN</code> statement. In PostgreSQL, you might use something like this to reduce the number of tables.</p> <pre><code>CREATE DOMAIN role AS VARCHAR(7) NOT NULL CHECK (VALUE in ('Admin', 'User', 'Guest')); </code></pre> <p>Followed by</p> <pre><code>CREATE TABLE user_roles ( user_id integer not null references users (id), role_name role ); </code></pre> <p>Replacing a table with a <code>CREATE DOMAIN</code> statement is most useful when the number of rows is stable and relatively small. Personally, I'd rather have the tables.</p> <p>If you stick with id numbers, you also need UNIQUE constraints on Roles.RoleName, Permissions.Description, and UserTypes.UserType.</p> <p>Otherwise, you seem to be doing fine.</p>
    singulars
    1. This table or related slice is empty.
    1. PONormalizing Foreign Keys with Junction & Lookup Tables
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike Sherrill 'Cat Recall'
    1. USMike Sherrill 'Cat Recall'
    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. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI posted a link to an image of the generic schema that I threw together in Access real fast (I'm developing in SQL Server 2008 R2 though, just to reiterate that). As for your suggestions, so you're saying that I only need two tables in place of four? Let me modify that schema to take into account what I think you're saying.
      singulars
      1. PO
      1. USChiramisu
    2. COActually, I don't like the having multiple records for each user-permission relationship idea. Seems messy to me, and, isn't this non-normal? Please forgive my ignorance, college really doesn't do squat to prepare you for real development. I did my best to get any experience I could, but it seems it still wasn't enough. Ugh.
      singulars
      1. PO
      1. USChiramisu
    3. COBtw, I was taught that whenever text is repeated in a table (such as the username in your example) that it should be replaced with an int foreign key and reference a lookup table. Would you say this is a good idea? It would only be a simply "join" to bring them back together in a query.
      singulars
      1. PO
      1. USChiramisu
 

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