Sql-server – Are #tables and #procedures ‘not best practice’ or ‘bad practice’

Table aliasing is a common and helpful practice.

• It saves you keystrokes when referencing columns anywhere in your query.
• It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
• It is even required when you join a table to itself or when you join to the same table multiple times. This is so the query optimizer knows which table you are referencing when you mention a column.

The following reporting extract illustrates all of the above points nicely:

INSERT INTO reporting.txns_extract
SELECT 
    -- 30+ columns snipped
    -- 
    -- Would you want to type out full table names for each 
    -- column here?
FROM 
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
    INNER JOIN  
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
    INNER JOIN  
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
    LEFT OUTER JOIN
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
    LEFT OUTER JOIN
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
    LEFT OUTER JOIN
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
    INNER JOIN
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
    INNER JOIN
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
WHERE
    -- ... and in the WHERE conditions here?
        ft_cdi.transaction_type <> 'Payment'   -- alias tells me this table is not for payments
    AND ft_cdi.status = 'Approved'
    AND ft_pmt.transaction_type =  'Payment'   -- alias tells me this table is for payments
    AND ft_pmt.status = 'Approved'
    AND ft_cdi.last_user_date >   ft_last_user_date_begin
    AND ft_cdi.last_user_date <=  ft_last_user_date_end
;

The first method is cleaner. I would make a few suggestions, however. You don't need the UserDocumentID surrogate Primary Key. A composite key of UserID and DocumentID would suffice, while also provide a unique constraint for the junction table. Also, is HasAccess really necessary? I believe the existence of the record in the junction table is enough to indiciate that access is granted.

Do you have different levels of access? Read-only, read/write, delete?

Hope this helps,

Matt