Before I even get started, let me apologize for the length of this post. In
two sentences - I'm asking if someone wants to give some (free) counsel and
guidance on a project I'm building. I'm looking for that exquisite and rare
combination of a STRONG senior SQL developer/data modeler who absolutely
LOVES to teach. If this post doesn't apply to you - or if it frustrates you
in some way -- *PLEASE* just mark it read and move on. I would love to
avoid a bunch of negative responses and/or lectures. I've seen some harsh
posts out there when people don't like what they're reading.

--------------------------

I get some absolutely great information from a small number of technology
newsgroups. Bob B. and Erland are amazingly smart about all things SQL.
It's fun to read and learn from other people's posts. So, I had a thought.
I've been looking for a mentor for a while. [[I'm not asking for a mentor
here in a newsgroup]] But, what I absolutely would LOVE is to get a
seriously smart data modeler that I can run my project by. I guess I'm
asking for someone who is a combination of a senior SQL database engineer
(specifically with strong data modeling skills) - BUT ALSO sees themselves
as a teacher-type person. I know there are certain people out there that
receive their own joy out of just helping others -- it's that combination of
the two that I hope to find.

We are only a few days from launching a web-based software site to a beta
group of around 300 people - but I'm looking for advice and guidance for
version 1.1 or 1.2. Honestly, I feel like my v1.0 data model held up pretty
well against all the requirements that were thrown my way for the site
getting ready to launch. On about a 40 table model, I think I had to add
one table, and one field in each of two other tables. I can't share much
here in a public forum, because our product may have national implications
(if a few things go the right way). If the product makes it past this beta
stage, it would find itself as a B2B system that would get licensed,
installed, and integrated into many other companies. So, just to give a
sense of scale, If I were making a wild guess, I could see the final model
having, maybe, ~75 to ~90 tables.?.?. Secondly, the owner of our company
has done some big-time things in the consumer space and has many years of
dealing with licensing and patent issues. Therefore, he's a real stickler
for security and ownership rights. But, on a one-to-one basis, I
could/would share everything that would be helpful.

Why am I asking for help now?? Well, on this project, my developers kept
complaining that my model was "too complex" for the project. I strongly
disagree(d). It held up very well - and the owner of our company has big
plans - I don't want to get stuck re-doing everything in 6 months - and ALSO
having to re-write all the freakin code to a new model. And, honestly,
we're making good use of all the tables - so it doesn't seem like there are
too many at all ------ to me. But, back to my question of "why now"? I
guess I found myself really lacking confidence in pushing back against the
developers who, whether it's true or not, feel like they have more
experience _using_ data models than I have _designing_ them. I mean, I did
do some pushing back - but I'm just really second-guessing myself. If I'm
not right, I'm wasting a bunch of developer time, and worse, I'm
over-complicating my job for years to come as we develop this product out...

I know people in newsgroups like details - and many seem to get particularly
frustrated (and rude even?) if they don't get them. So, I'll try to
articulate and be extremely specific about some of the things I would love
to get help with - and to learn from a senior DB engineer.

* Someone who loves to "teach". I don't have any budget to pay someone, so
I'm looking for that rare crossing of a person who has the technical skills
AND actually derives enjoyment out of learning new things and helping
others.
* Someone who has seriously strong data modeling skills. I'm on SQL Server
2008R2, but I'm not sure the specific DB technology makes a huge difference
in my data modeling request. Now, if we start talking implementation,
someone with Microsoft technology skills might be helpful. But the data
modeling is my first priority.
* I picture sending my data model to someone with a pretty long description
of the "why" I built it the way I did - explaining the business logic and
business implications, some sample data that shows how the pieces fit
together. Actually, now that I'm typing this out - it's occurring to me
that our website will be up - so you will get to just see the database (from
the outside obviously) and some of the business logic in action - with REAL
data. I could also send over a backup of the database through dropbox or
something.
* I'd love to get on the phone a couple of times - maybe using a screen
sharing website and talk through the model - exchange email questions every
once in a while with very specific questions - get advice - ask questions -
hear answers - talk about implications of one idea versus another - get
names of books I should be reading - trusted website resources - the best
data modeling tools that get used by real senior SQL developers -
hmmm...what else? Oh yes, "Been-there-done-that" advice. I guess maybe
this is one of the most important things I'm asking for - someone who can
say, from years of experience: yeah, I know that's what the books and
teachers say, but here's how it works in the real world. Maybe that's it
for now.
* More specifically, when it gets right down to the data model itself, I
have questions like: I make heavy use of "cross tables" for many-to-many
relationships - I even want to learn small things like -- if I were in a
room of senior data modelers - would they laugh at me if I called it a
"cross" table? Or, would that be perfectly fine/normal/expected/etc.? For
my cross tables, I always use something like *x_[table1]_[table2]*. I make
heavy use to "super type" kinds of tables, like, if I have a *list* table, I
will often have a *list_type* table with the #list_type_id# field as a
foreign key in *list*. I use *attribute* tables to combine lots of facts
about things. Another big one, when do -you- make the decision to fit some
new concept into a series of kind-of-generic tables where it would easily
fit versus creating a new series of tables to handle the new concept -
especially when the table names end up sorting of looking very similar.
Again, this one is more about real-world experience == field naming
conventions - there are so many ways you could choose to do this. What
really makes sense on a 5 year long project. What do you find yourself
looking back on 3 or 4 years later and saying - man, I really wish I had
named everything following this convention or that. How to prepare for
scale-up from a small(ish) data model to a much bigger, medium- to
large-scale project. Views/Stored Procs/Triggers, etc. - when, where, why,
how much is too much? Stored Procs - I'd love a quick conversation about
how I'm writing them? Am I completely screwing things up - or am I doing
okay? Since we're a Microsoft shop, the new site is built in MVC (.NET4)
with Entity Framework...so my developers laugh when I implement something
with sprocs. They want to use LINQ against the DB for everything. It does
seem like the right way to go - and I've gotten fairly good with LINQ --
but, again, am I making the right choice? I'd love thoughts from someone
who has real-world experience. As I'm typing this stuff out, I'm realizing
that it could sound very, very, very basic - I mean, how to name fields in a
database?! Come'on, are you kidding me! But, I absolutely know, without a
doubt, that people who have been doing this for years, have learned a thing
or two about what they would do differently if they had a clean slate. It's
that experience I'm looking for...and I know that I'm not getting that from
the books I'm reading - or from developers who have only worked on smaller
projects and don't know any better.
* Currently I use BIDS to move data from a legacy POS/CRM/ERP retail
system. So, although I'm not super sophisticated with BIDS, I am totally
capable of get data moved in and out of systems - but I would love to know
more about how to make sure I'm maximizing my use of this core tool. Again,
I'm not asking for someone to teach me how to use it -- just love to talk
conceptually about when/where/how/why you might introduce this into the
overall platform.
* Then, just see where things go from there. I'm not sure how I could yet,
but I would ABSOLUTELY LOVE to reciprocate in helping someone with anything
I was capable of doing.

Thanks guys and gals -
Kurt
kdicus (AT) dicus (DOT) com
(425) 408-3250 (Seattle area)
[not sure if you can put contact information here!]

Kurt Dicus (kdicus (AT) dicus (DOT) com) writes:
I know several of my MVP colleagues who are very passionate about
teaching SQL and data modelling. And while I don't work with teaching
as such, I certainly answer posts in newsgroups and forums not only to help
people to get over their current obstacle, but I want them to learn for
the future.

However, it is not very likely that you will find someone who will help
you on the wide scale you are asking for for free. Least of all not
since you work for a commercial enterprise. To be blunt, you are asking
for a consultant, and people who offer to help, will quote their
regular rate. At least they should, in my opinion.

I hope you apologize that I am this blunt, but the alternative would have
been to leave the post in silence.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

I've debated in my own head with how to respond to this. So, let me start
by saying, I *very* much respect everything I've ever seen from you [as much
as you can tell by way of a newsgroup, of course!] I fear this post will
come across as defensive -- but from the bottom of my heart, I promise you
that it is coming from a humble place. To that end, I give you my word that
I will not keep pushing this. With this little bit of new information, if
you still believe I'm looking for a consultant -- versus a big-brother
type -- so be it...I'll trust your judgment. If you'll indulge me a few
hundred more words, however, I would like to provide a very slight change in
perspective to see if it changes your opinion.

I worried when I was writing such a detailed post before that it might sound
HUGE. I tried to balance this against asking for so much and giving too
little... People seem to get hammered on these newsgroups when they don't
give enough information... I would bet that we're talking about a total
commitment of less than 2 or 3 hours. Now, I'm not saying that isn't a
significant amount of time to invest in someone you don't even know, but
what I guess I mean is - it's not tens of hours. Also, I'm not looking for
anyone to *do* anything for me. I can implement anything I learn. I just
want to be able to ask someone a question and truly know that what I
hear...I can trust. Here is a very specific example of something I'm
talking about - and why I really feel like my questions might just take
minutes to answer...not hours.

One question I have - and I almost feel silly for asking -- but when it gets
right down to the nitty-gritty and I have my modeling tool open in front of
me, I don't have anyone to ask...

Here is an example of how one of my tables might look:
----------------
"list" table
----------------
list_id (uniqueidentifier, NN)
customer_no (uniqueidentifier, NN, FK)
list_name (varchar(255), NN)
....[other columns]...
....[other columns]...
list_order (int, Null)
list_type_id (uniqueidentifier, NN, FK)

So, my silly question, should I be using the prefix of 'list' on each
column? I don't do this 100% of the time - but I do unless I have a very
good reason not to. I can easily make an argument for it -- and against it.
I'd love to just have someone say, "...in the long run, option A ends up
usually working out better...". I do web searches, of course, but there are
dozens and dozens of opinions - and you have NO idea who you can trust
(since every answer is slightly different).

You can see, this is probably a 2-minute conversation with an experienced
designer. I just have a handful of these kinds of questions...

Alright, enough is enough...that's all the typing I'll put into my rebuttal.
Again, thanks for everything you put into these newsgroups for all of us.

Thank you for your consideration.
Kurt

------------------------


"Erland Sommarskog" wrote in message
news:Xns9F66ED376126EYazorman (AT) 127 (DOT) 0.0.1...

Kurt Dicus (kdicus (AT) dicus (DOT) com) writes:
I know several of my MVP colleagues who are very passionate about
teaching SQL and data modelling. And while I don't work with teaching
as such, I certainly answer posts in newsgroups and forums not only to help
people to get over their current obstacle, but I want them to learn for
the future.

However, it is not very likely that you will find someone who will help
you on the wide scale you are asking for for free. Least of all not
since you work for a commercial enterprise. To be blunt, you are asking
for a consultant, and people who offer to help, will quote their
regular rate. At least they should, in my opinion.

I hope you apologize that I am this blunt, but the alternative would have
been to leave the post in silence.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

I've debated in my own head with how to respond to this. So, let me start
by saying, I *very* much respect everything I've ever seen from you [as much
as you can tell by way of a newsgroup, of course!] I fear this post will
come across as defensive -- but from the bottom of my heart, I promise you
that it is coming from a humble place. To that end, I give you my word that
I will not keep pushing this. With this little bit of new information, if
you still believe I'm looking for a consultant -- versus a big-brother
type -- so be it...I'll trust your judgment. If you'll indulge me a few
hundred more words, however, I would like to provide a very slight change in
perspective to see if it changes your opinion.

I worried when I was writing such a detailed post before that it might sound
HUGE. I tried to balance this against asking for so much and giving too
little... People seem to get hammered on these newsgroups when they don't
give enough information... I would bet that we're talking about a total
commitment of less than 2 or 3 hours. Now, I'm not saying that isn't a
significant amount of time to invest in someone you don't even know, but
what I guess I mean is - it's not tens of hours. Also, I'm not looking for
anyone to *do* anything for me. I can implement anything I learn. I just
want to be able to ask someone a question and truly know that what I
hear...I can trust. Here is a very specific example of something I'm
talking about - and why I really feel like my questions might just take
minutes to answer...not hours.

One question I have - and I almost feel silly for asking -- but when it gets
right down to the nitty-gritty and I have my modeling tool open in front of
me, I don't have anyone to ask...

Here is an example of how one of my tables might look:
----------------
"list" table
----------------
list_id (uniqueidentifier, NN)
customer_no (uniqueidentifier, NN, FK)
list_name (varchar(255), NN)
....[other columns]...
....[other columns]...
list_order (int, Null)
list_type_id (uniqueidentifier, NN, FK)

So, my silly question, should I be using the prefix of 'list' on each
column? I don't do this 100% of the time - but I do unless I have a very
good reason not to. I can easily make an argument for it -- and against it.
I'd love to just have someone say, "...in the long run, option A ends up
usually working out better...". I do web searches, of course, but there are
dozens and dozens of opinions - and you have NO idea who you can trust
(since every answer is slightly different).

You can see, this is probably a 2-minute conversation with an experienced
designer. I just have a handful of these kinds of questions...

Alright, enough is enough...that's all the typing I'll put into my rebuttal.
Again, thanks for everything you put into these newsgroups for all of us.

Thank you for your consideration.
Kurt

------------------------

"Erland Sommarskog" wrote in message
news:Xns9F66ED376126EYazorman (AT) 127 (DOT) 0.0.1...

Kurt Dicus (kdicus (AT) dicus (DOT) com) writes:
I know several of my MVP colleagues who are very passionate about
teaching SQL and data modelling. And while I don't work with teaching
as such, I certainly answer posts in newsgroups and forums not only to help
people to get over their current obstacle, but I want them to learn for
the future.

However, it is not very likely that you will find someone who will help
you on the wide scale you are asking for for free. Least of all not
since you work for a commercial enterprise. To be blunt, you are asking
for a consultant, and people who offer to help, will quote their
regular rate. At least they should, in my opinion.

I hope you apologize that I am this blunt, but the alternative would have
been to leave the post in silence.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 2011-09-21 07:27, Kurt Dicus wrote:

The Gettysburg Address was shorter than your original post. And far more to
the point.

People who frequent newsgroups do so in their own free time (usually). Make
it easy and fun for them to help you. Ask focused questions, one question
per post (unless they're inseparable). If people want to answer them, they
will. Otherwise they won't. Long personal screeds and extended pleas for
help are enjoyable to almost no one, so your chances of getting help quickly
diminish.

An argument can be made for prefixing the key columns (that will be used for
foreign keys) so they can have the same name in the referring table without
name clashes, e.g.

lists
list_id
name
order
type_id

types
type_id

list_options
list_id
name
value

This is how the SQL Server system tables do it: everything that refers to an
object_id calls the column "object_id", even sys.objects itself. This is
convenient when you're writing joins.

This is still not how I do it (or most other people in the world) because
prefixing columns with table names is never wrong and, if you apply that
consistently, there's no need to do anything special for the IDs.

Doing it for *all* columns is excessive because most queries refer to only
one table, and seeing the table name every time gets really redundant, e.g.

SELECT list_id, list_order, list_type_id,
list_list_list_list_baked_beans_and_list
FROM lists

SQL already has a built-in, opt-in mechanism for disambiguating columns, and
it's prefixing the table name:

SELECT lists.id, lists.order, lists.type_id, types.name type_name
FROM lists JOIN types ON lists.type_id = types.id

Plural vs. singular is mostly religious. Arguments can be made either way:
if you see the table as a type name, you'd use the singular (it makes column
prefixes more sensible), if you see it as a collection of rows, the plural
makes sense (it's more descriptive of what the table is, a collection). Pick
something that goes well with your morals.

For naming, it's mind-bogglingly more important that you be consistent
across your entire database than that you use a particular scheme. I've seen
databases made by VB programmers where every table was the plural, prefixed
with "tbl". This is a nonsensical convention, but it's still a convention,
and as long as I don't have to *think* about what the table that contains
the user rows could be called ("users" or "tblUsers" or "user" or
"benutzer") or what its ID column is called ("id" or "ID" or "user_id" or
"userID" or...) but can extrapolate from the scheme it doesn't matter.

Once you've settled on a scheme, never deviate from it no matter how stupid
it is and never brook arguments from people who want to point out that it's
stupid (unless they're just doing it to make sure the next database will be
less so). Obviously, changing it once it's in production is ludicrously
expensive, so complaining about it more than once does no one good.

Well allow me to present my credentials: I'm just another guy on the
Internet. I hope my post has value on its own. If not, no harm, I gave it
away for free.

--
J.

Kurt Dicus (kdicus (AT) dicus (DOT) com) writes:
Consultants come in many shapes. For me, a consultancy gig might be
just what you asked for: review code, database design, discuss, come
with suggestions etc. But not writing any code of my own.

And, no doubt, if you are willing to pay, you should have no problem
finding anyone who is willing to be your mentor.

That did not come across from your original post, or I did not read it
carefully enough. To me it sounded like it was a longer effort. Mind you,
if I have to review the full data model of an application that is more
than 2-3 hours. Only learning the business may take a day or two. But, of
course, if you only want to discuss details, it's a different story.

I would say that this is one of these questions where the answer is
very much up to habit, although I personally do not advocate it.

On the one hand, if all column names in the database are unique,
you don't have to use column prefixes in your queries. Or rather,
you are obliged to use them, since they are part of the column name.

On the other hand, it makes questions "in which tables are there an
'orderid' column?" more difficult to answer. Then again, since some-
times you may need two order ids in a table, and then you need different
names on them?

I use prefixes on columns where the name would be too non-descript
without id. I would never call a column ID, but I would call it
orderid, customerid, insid etc. But I would not call a city column
in an address table address_city; I would just call it "city".

This is one of these questions you can spend a whole day discussing,
but when the day is over, you will not have improved your data-
modelleing skills - this is not a very important question.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx