SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help me in designing table and its indexes


Help me in designing table and its indexes

Author
Message
SQLAddict01
SQLAddict01
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 585
CREATE TABLE FileMeta(Id [Some Data Type] NOT NULL, ParentId [Some Data Type] NOT NULL, Type CHAR(1) NOT NULL /* F = File or D = Directory */, AccountId [Some Data Type] NOT NULL, Name VARCHAR(255))

SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Id = [Bar]

SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Name = ‘[Some Name]’
trying to understand and design indexes
how would you choose indexes?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)

Group: General Forum Members
Points: 895342 Visits: 48655
https://www.sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)

Group: General Forum Members
Points: 858652 Visits: 47076
SQLAddict01 - Friday, March 2, 2018 3:07 PM
CREATE TABLE FileMeta(Id [Some Data Type] NOT NULL, ParentId [Some Data Type] NOT NULL, Type CHAR(1) NOT NULL /* F = File or D = Directory */, AccountId [Some Data Type] NOT NULL, Name VARCHAR(255))

SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Id = [Bar]

SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Name = ‘[Some Name]’
trying to understand and design indexes
how would you choose indexes?


You're trying to build a "Parent/Child" table, which is frequently referred to as an "Adjacency List". I see by your comments that it's going to be used for file directory structures in some way.

I also see that you're setting yourself up to perpetuate the need for RBAR to traverse the directory structure. That means that you're actually missing a few columns if you want whatever project you're working on to result in a very high performance solution and so we can't yet recommend any indexes because the columns you actually need indexes on are missing.

Please see the following articles on a very high performance hybrid method not only for making it so queries against the hierarchy run insanely fast, but maintenance of the hierarchy is easy and even more insanely fast.

Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

Also and just in case you don't know (many don't), be careful where you use the tokens of "foo" and "bar" in any of your discussions or code because you might not know what the source of those names are but someone you speak to may. They're a derivative the pronunciation guide (fo͞obär) of the military slang term "FUBAR", which is a NSFW (Not Safe For Work) term especially in the presence of people that are sensitive to swear words. I won't even use the terms in code and especially not as examples of code (and, yes, I was in the military). Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
DesNorton
DesNorton
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18044 Visits: 7593
I would also consider separating Directories and Files into their own tables.


How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)

Group: General Forum Members
Points: 858652 Visits: 47076
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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
DesNorton
DesNorton
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18044 Visits: 7593
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.



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
DesNorton
DesNorton
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18044 Visits: 7593
DELETED - Somehow managed to double post



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)

Group: General Forum Members
Points: 858652 Visits: 47076
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RonKyle
RonKyle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26416 Visits: 4426
This might be a case where it depends what you want to do with the data and no obvious clear answer at the outset. An example to compare it to would be street address. In most cases the number and street would be in the same field. But that might not give someone who sells houses the needed granularity. In this case there would be one field for the street, and another for the address. Two tables rather than columns are being discussed, but the intent is the same. It seems to me that generally these would be combined. But if the requirement is to take certain measurements that this combination would make more difficult, then better to separate.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)SSC Guru (858K reputation)

Group: General Forum Members
Points: 858652 Visits: 47076
SQLAddict01 - Friday, March 2, 2018 3:07 PM
CREATE TABLE FileMeta(Id [Some Data Type] NOT NULL, ParentId [Some Data Type] NOT NULL, Type CHAR(1) NOT NULL /* F = File or D = Directory */, AccountId [Some Data Type] NOT NULL, Name VARCHAR(255))

SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Id = [Bar]

SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Name = ‘[Some Name]’
trying to understand and design indexes
how would you choose indexes?

So, back to you, SQLAddict01 . What are you actually going to use this table for? What is the business logic that you will attempt that this table is supposed to help solve? Of course, the reason for me asking is because you haven't actually defined the problem that the table will be used to solve and so all of our recommendations (and yours) may be out in left field.


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search