Home Forums SQL Server 2016 SQL Server 2016 - Administration Help me in designing table and its indexes RE: Help me in designing table and its indexes

  • March 4, 2018 at 1:43 pm

    #1981891

    DesNorton - Saturday, March 3, 2018 10:32 PM

    Jeff Moden - Saturday, March 3, 2018 5:52 PM

    DesNorton - Saturday, March 3, 2018 1:00 PM

    I would also consider separating Directories and Files into their own tables.

    I'll suggest against putting those into their own tables.  It would be like putting managers and employees in their own tables, which pretty much screws the whole notion of the hierarchy.

    If you have the files and directories in the same table, then you can also use the method in the article at the second link to get byte totals for all levels of all directories.

    I see Managers and Employees as both being Employees, therefor keeping 1 table, with an EmployeeType field.
    However, I see Directories and Files as completely separate entities, hence the normalisation.

    That said, the ability to get to the byte totals would be a compelling reason to demormalise in this case.

    I wouldn't call it a denormalization but can understand why some might. I just look at directory names and files names as manager and employee names in that they just named parts of the same hierarchy.  Some can have children (directories) and some cannot (files).  Directories have a name with zero length and files have names with typically a non-zero length.  They're just attributes for a general entity.  Putting directories and files in different tables would be like putting vehicles in different tables just based on what color they are or whether or not they have 4 wheel drive.  Look at sys.objects.  They're all objects and they have different attributes such as being a view or a table, etc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)