Detailed Notes
1. Standards 2. Tables names 3. Column names 4. Ids and keys 5. Stored Procedures 6. Functions 7. Parameters and variables.
Transcript Text
[Music] well hello and welcome back we're continuing our season where we're looking at my sequel uh tutorial type things and we're sort of getting to the the end of some of the things i wanted to cover we've gotten through a lot of basics one of the things we have not talked about is names and naming standards i want to spend a little time on these because it is something that does matter and is very helpful particularly when you start getting into larger databases as in a large number of tables or a large number of stored procedures or even complicated stored procedures because it's very much like coding in general you want people people to be able to jump in and have some clue about what you're doing and where things are the first thing i want to look at is table names and whoop let me flip back over here there are a couple of different ways to do it the first one to look at here show you this is this list here are tables that are generated by uh django sort of the way it builds stuff by default you build out models it's got its own little structure and then based on the names of classes and objects it builds out table names the thing that's worth considering um and that they do is one is if you look you have like these you know the auth underscores and you have these django underscores and rss app is the name of the application itself so in this case you have some these auth tables are for data around authorization the django are around its its internal storage and things like that and then you have the application tables which have all of their various you know names and organization now the nice thing about the thing about with table names is that you're probably going to see them in a list like this i mean even if i do like show tables over here let me see this long list now in this case they're not you know they're not ordered or anything like that um but normally i'm going to see it in some way that's going to be ordered probably by name but i can also do some things that i can look for names based on uh based on like a string within the name in particular it's nice to have things grouped uh for example here i've got these feed related things are all together because they're the app and then feed i use lkp for lookups but i've seen uh xref i've seen a lot of sometimes just l or list or things like that for uh you know look up or relational type stuff like if i look at the data here it's just got you know a couple different little records and this is just as a sort of like a category type field sometimes it starts with cat but what that does is that pushes those together when you're looking at a list and it makes it easier to find what you're looking for particularly you know like if you look here and i've granted this is all databases but if these were columns you know it can get it could get long fast and so in this case that's uh or in a case when you're using you know these correctly like here these are these are all the things that come off of a user then you can group them by name so you want to be consistent is really the key so that it if somebody's looking for something they're going to be able to see it you know for example like user you know maybe you start with a there's a user table and then there's user address user contact user blah blah blah so you'll all you know be able to see oh user and then here are the things that are the um the sort of the details or the relationships that come off of that that's not always easy you know it's not always possible like in this case we have feeds and then we have users but you don't have feed user instead you have user feed but at least you know between the two you can pick one of them now in this case what django does is it uses all lowercase and underscores to separate things to sort you know to highlight where the differences are slightly different approach that you can take which i did which was not as consistent here you can see these lkps do not start with a capital l which originally was done in hopes that those would all float to the top because they would be lower case or float to the bottom didn't quite work out but these use um other than that they use camelcase which can be useful to you know when you get into something that's a little longer name then you can do three or four words and it's still sort of easy to pick them out but then you run into case sensitivity issues sometimes with the database with the sql so it's actually would be easier to instead of using camel case is use you know underscores or something along those lines so that you separate the words and make it very easy then to read particularly if you look here if these were all underscores instead of being pushed together it would be feed feed underscore item feed underscore out feed underscore out underscore item and so you'd be able to see you know you'd see a list of names so um let me just pick like oh i just do this so you'd be see table like user and then you'd see user address user address uh let's say i don't know why i want to do that but let's say user contact and these would not necessarily in order uh user employer user employer i don't know details you know something like that just throwing a few of these together so you could pretty quickly see that hey this is the user but here these are user things and sometimes you'd see something like user user contact use your contact phone here's your contact email you know something like that that would even be details off of a detail if you get really complex and you know sort of long complicated names so there's something that is depending on the language you're working in stuff like that you may be very used to camel case however that can cause some you know some issues particularly when you get into aliasing things because depending on how you alias stuff you know if it's case sensitive you can get confusing quickly uh which sometimes makes it within your aliasing useful to you know say so if i wanted to do uh let's say from here let me go to this guy where's our rss reader so if i go from here and let's just do select star from uh let's do our session feed so here uh when i'm doing that if i wanted to do i could do like rf and so it'd be nice in lowercase and then it's just rf dot there and then i've got my alias or you know if i do as but if i do or maybe i want to be consistent always going to do it's my aliases are always going to be all cap if i'm case sensitive then that also is is you know pretty readable and actually would fit with most people would do it like this they keep all of the keywords all capped so that may or may not be an issue oh you may also do like let me call this like i'll start everything with an a so it'd be alias or maybe an a underscore even and so now i could see real easy that i've got this oh this a underscore is an alias and then i can go find that somewhere in there so when you're thinking about it as far as trying to set some sort of a standard um think about what how you want to do it with aliases as well and again the key to this is that it's a standard and it's something you stick with if you bounce around this could be really hard to to read your code that's tables with names typically the way a table is done is that it doesn't have an s at the end because each row is one thing by it's almost by default they should all have s's because they're all going to have multiple records but uh that is not always held uh you know for example here and the way they did it with django is it's you know you have group but you have group permissions because that's basically pointing back to say that it is a mini to one relationship um and so you've got you know so a user there can be multiple groups assigned to a user um the admin log it's just your normal log records um where they've got the s's typically is you know it has its own inner meaning but that can be sort of complex to nuanced and it's usually easier to just say you know i'm a uh this is what i you know we're either we're always singles or always doubles another thing is ids it is easiest i think well one you should always have we've talked about this you should always have a single primary key that is not that is just some generated number has nothing to do with the data that way if the data changes the id never needs to change then you should always be able then to find that record but based on that id that id will always forever go to that record unless it gets deleted now some places like sometimes it's a table name underscore id so in this case it'd be like rss app underscore lookup target underscore id it's easier for it to just be id everywhere because then you don't have to worry about you know did they how did they do it based on the table name um the issue the and that means that every table when you have an id you know that that's that record that table's id you may also do a pk so it'd be like pk underscore id or something like that to say it's a primary key the the challenge sometimes get when you have when you call it id is when you're using foreign keys because then it becomes inconsistent so if i go here and i've got um let's see let me do this so like if i take user q i don't even know what user q is oh i did but uh let me go actually flip to this guy let me go over to this one because it's got less data and it has a slightly different approach so here like this one is now instead of using id i actually use the table name and the the column because when you get into foreign keys like you do here this contract has an id so that's the primary key for that row but then it also has foreign keys out to a performer in the performance table and so i can go to that performer id is going to be equal to performer id in this table so sometimes it's nice to do table id or table name with id or some designator that's the primary key so that you can actually keep the column names uh they'll be simple you know so if i wanted to link i could do um select star from contract oops except for it's probably capital c and then enter join performer on contract dot performer id equals perform performer dot performer id so it's the same whoops so i'm using that same column name in each and then that's going to link those together of course don't have any records so it doesn't help but that can be a useful kind of thing is to just say okay i should be able to find that and it's useful without looking at relationships or keys or anything else because you say hey if i've got a performance id you can look at that which i did i didn't even know but i and looking at those i said oh then that means since this is performer id this links to the performer table the id and the performer table this performance links to the the id and the performance table so doing those using those sorts of name standards can make it really easy to see relationships without having to deal with keys and things of that nature now when you get into foreign keys and primary keys and indexes and things of those natures it's one most places most databases they have to be unique within the system thus and i think we've mentioned this it really makes sense for your keys to be the uh usually to be the table name and then the uh the column name or the index the yeah the column name that it's on now in this case like see this one doesn't have to have it so it has performance id but if you look at performance um it has a primary and so it's like this one's not i'm not seeing name collisions uh i don't think anywhere um i may have just lucked out but i don't think i'm going to see anything oops indexes uh preference type remember yeah so there's probably going to be a bunch of member ids maybe not uh well i'm sort of looking right but i may have looked out that these had oh and i wonder if these are actual names but in order to make it uh unique typically what you would do is like if i wanted to create an index on the final field in contract then you know i'd be create index uh contract final on contract final something like that and then it's like again it's very easy when you look at an index to figure out where it's at so if you start having issues if you have some sort of index error that comes back you know particularly unique and stuff like that you can tell oh it's on the contract table i've got to go look at the final column and i'll and actually i can look in these indexes on this table and i'll be able to see what that one is and see why there's an issue with it or if you're try if your performance going through some performance improvements then you can see which indexes are being used for stored procedures and functions um much like functions and usually like you get you're going to want to sort of mirror probably the primary language that's using it so whether it's camel case or whether it's lowercase or uppercase a thing to remember with functions is they're going to be potentially used quite a bit within queries so you want you really want to avoid like huge obnoxious function name length stored procedures you're going to like you know you may be select you know star from my big procedure name and then give it some some parameters the function may exist in 10 different places because a function could be things like uh force things to uppercase so if i have to do that for five fields in a table and my name my function name is force all of the characters in this two uppercase that's a long name and it's really annoying to write that several times so you want it to be meaningful but concise and you might even do some uh it but you got to be consistent maybe and do a little bit of abbreviations and things like that just to make it a little easier to use and again you're going to want to do some sort of in the same vein as tables with stored procedures and functions you want to you know ideally order the words within the name so that they are grouped if they are similars for example with store procedures a lot of times it makes sense to start with the primary table name that it works on and then that will you know when you sort them it will group all of those together not to mention just you know makes things look a little better when it's consistent and you're going to run into the same things whether you want them all lower case or camel case or upper case just be consistent in how you do it and then with parameters and variables again those are all over the place as far as you know the companies and organizations i've worked with so i think it's just pick something uh some place it's just like variable names in any other language sometimes people want to precede them uh have some sort of prefix that says what type it is you know like all the integers would start with a lowercase i all the strings would start with the lowercase s or the var chars and you know may start with a lowercase b things like that um just find a way to be consistent or check your you know if you're an organization ask if they have a standards document and you know either go by that or if it's missing some stuff if it has gaps don't be afraid to say hey this there's a couple things i have questions about why don't we create standards for those as well now i i know i've gotten a little just theoretical a little bit i guess in this a little more academic but i think it's important to think about these things as we're getting into database development in particular so i think i'll just sort of uh stop beating the dead horse now and wrap this one up and let you get back to your day so go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we're
continuing our season where we're
looking at my sequel uh tutorial type
things
and we're sort of getting to the the
end of some of the things i wanted to
cover
we've gotten through a lot of basics
one of the things we have not talked
about is names and naming standards
i want to spend a little time on these
because it is something that does matter
and is very helpful particularly when
you start getting into larger
databases as in
a large number of tables or a large
number of stored procedures
or even complicated stored procedures
because it's
very much like
coding in general you want people people
to be able to jump in and
have some
clue about what you're doing and where
things are
the first thing i want to look at is
table names
and whoop let me flip back over here
there are a couple of different ways to
do it
the first one to look at here show you
this is
this list here
are tables that are generated by uh
django
sort of the way it
builds stuff by default you build out
models it's got its own little structure
and then based on the names of classes
and objects
it builds out table names
the thing that's worth considering
um and that they do is one is if you
look you have like these
you know the auth underscores and you
have these django underscores and rss
app is the name of the application
itself
so in this case you have some
these auth tables are for
data around authorization
the django are around its
its internal storage and things like
that
and then you have the application tables
which have all of their various you know
names and organization
now the nice thing about the thing about
with table names
is that you're probably going to see
them in a list like this i mean even if
i do like show tables
over here
let me see this long list now in this
case they're not
you know they're not ordered or anything
like that
um
but normally i'm going to see it in some
way that's going to be ordered probably
by name
but i can also do some things that i can
look for names based on
uh based on like a string within the
name in particular
it's nice to have things grouped uh for
example here i've got these
feed related things are all together
because they're the app and then feed
i use lkp for lookups but i've seen
uh xref i've seen a lot of sometimes
just l or list or things like that for
uh you know look up or
relational type stuff like if i look at
the data here it's just got you know a
couple different little records and this
is just as a
sort of like a category type field
sometimes it starts with cat
but what that does is that pushes those
together
when you're looking at a list and it
makes it easier to find what you're
looking for particularly you know like
if you look here and i've granted this
is all databases but if these were
columns you know it can get
it could get long fast
and so in this case
that's uh or in a case when you're using
you know these correctly like here these
are these are all the
things that come off of a user
then you can group them by name so you
want to
be consistent is really the key
so that it
if somebody's looking for something
they're going to be able to see it you
know for example like user you know
maybe you start with a there's a user
table
and then there's user address user
contact user
blah blah blah
so you'll all you know be able to see oh
user and then here are the things that
are the
um the sort of the details or the
relationships that come off of that
that's not always easy you know it's not
always possible like in this case
we have feeds
and then we have users but you don't
have feed user
instead you have user feed but at least
you know between the two you can pick
one of them
now in this case what django does is it
uses all lowercase
and
underscores to separate things to sort
you know to highlight where the
differences are slightly different
approach that you can take which i did
which was not as consistent here you can
see these lkps do not start with a
capital l
which originally was done in hopes that
those would all float to the top because
they would be lower case or float to the
bottom
didn't quite work out
but these use um other than that they
use camelcase
which can be useful to you know when you
get into something that's a little
longer name then you can do three or
four words and it's still
sort of easy to pick them out
but
then you run into case sensitivity
issues sometimes with the database with
the sql
so it's actually would be easier to
instead of using camel case is use you
know underscores or something along
those lines so that you separate the
words and make it very easy then to read
particularly if you look here if these
were all underscores
instead of being pushed together it
would be feed feed underscore item feed
underscore out feed underscore out
underscore item
and so you'd be able to see you know
you'd see a list of names so um
let me just pick like oh i just do this
so you'd be see table like user and then
you'd see user
address
user
address
uh
let's say
i don't know why i want to do that but
let's say user contact and these would
not necessarily in order
uh
user
employer
user employer
i don't know details you know something
like that
just throwing a few of these together so
you could pretty quickly see that hey
this is the user but here these are user
things
and sometimes you'd see something like
user
user contact
use your contact
phone
here's your contact email you know
something like that that would even be
details off of a detail if you get
really complex and you know sort of long
complicated names
so there's something that is depending
on the language you're working in stuff
like that you may be very used to camel
case however
that can cause some you know some issues
particularly
when you get into aliasing things
because
depending on how you alias stuff you
know if it's case sensitive you can get
confusing quickly
uh which sometimes makes it
within your aliasing
useful to you know say so if i wanted to
do
uh
let's say from here
let me go to this guy where's our rss
reader so if i go from here
and let's just do select star from
uh
let's do our session
feed so
here
uh when i'm doing that
if i wanted to do
i could do like rf and so it'd be nice
in lowercase and then it's just rf dot
there
and then i've got my alias or you know
if i do as but if i do or maybe i want
to be consistent always going to do it's
my aliases are always going to be all
cap
if i'm case sensitive then that also is
is you know pretty readable and actually
would fit with most people would do it
like this they keep all of the keywords
all capped so that may or may not be an
issue oh you may also do like let me
call this like i'll start everything
with an a so it'd be alias
or maybe an a underscore even and so now
i could see real easy that i've got this
oh this
a underscore is an alias and then i can
go find that somewhere in there so when
you're thinking about it
as far as trying to set some sort of a
standard
um think about what
how you want to do it with aliases as
well
and again the key to this
is that it's a standard and it's
something you stick with
if you bounce around this could be
really hard to
to read your code
that's tables
with names typically
the way a table is done
is that it doesn't have an s at the end
because each row is one thing by
it's almost by default
they should all have s's because they're
all going to have multiple records
but
uh that is not always held uh you know
for example here and the way they did it
with django is it's you know you have
group
but you have group permissions because
that's basically pointing back to say
that it is a mini to one relationship
um
and so you've got you know so a user
there can be multiple groups assigned to
a user
um the admin log it's just your normal
log records um where they've got the s's
typically is you know it has its own
inner meaning
but that can be sort of complex to
nuanced and
it's usually easier to just say
you know i'm a uh this is what i you
know
we're either we're always singles or
always doubles another thing is
ids
it is easiest
i think
well one you should always have we've
talked about this you should always have
a single primary key that is not
that is just some generated number has
nothing to do with the data that way if
the data changes the id never needs to
change then you should always be able
then to find that record but based on
that id that id will always forever go
to that record unless it gets deleted
now
some places like
sometimes it's a table name underscore
id so in this case it'd be like
rss app underscore lookup target
underscore id
it's easier for it to just be id
everywhere because then you don't have
to worry about you know did they how did
they do it based on the
table name
um
the issue the and that means that every
table when you have an id you know that
that's that
record that table's id
you may also do a pk so it'd be like pk
underscore
id or something like that to say it's a
primary key
the
the challenge sometimes get when you
have when you call it id
is when you're using foreign keys
because then it becomes inconsistent so
if i go here and i've got
um
let's see
let me do this
so
like if i take
user q i don't even know what user q is
oh i did but uh let me go actually flip
to this guy let me go over to this one
because it's got less data
and it has a slightly different approach
so here
like this one is now instead of using id
i actually use the table name and the
the column because when you get into
foreign keys like you do here
this contract has an id so that's the
primary key for that row
but then it also has foreign keys out to
a performer in the performance table
and so i can go to that performer id is
going to be equal to performer id in
this table so sometimes it's nice to
do table id or table name with id or
some
designator that's the primary key so
that you can actually keep the column
names uh they'll be simple you know so
if i wanted to link i could do
um
select star from contract oops except
for it's probably capital c
and then enter join
performer
on
contract dot
performer
id
equals
perform performer
dot performer id so it's the same
whoops
so i'm using that same column name in
each and then that's going to link those
together of course don't have any
records so it doesn't help but
that can be
a useful kind of thing is to just say
okay i should be able to find that and
it's useful without looking at
relationships or keys or anything else
because you say hey if i've got a
performance id you can look at that
which i did
i didn't even know but i and looking at
those i said oh then that means since
this is performer id this links to the
performer table the id and the performer
table this performance links to the the
id and the performance table
so doing those
using those sorts of
name standards can make it really easy
to see relationships without having to
deal with keys and things of that nature
now when you get into
foreign keys and primary keys and
indexes and things of those natures
it's
one most places most databases they have
to be unique within the system thus and
i think we've mentioned this it really
makes sense for your keys to be the uh
usually to be the table name
and then
the uh
the column name or the index the yeah
the column name that it's on now in this
case like see this one doesn't have to
have it so it has performance id but if
you look at performance
um
it has a primary
and so it's like this one's not i'm not
seeing name collisions uh
i don't think anywhere
um
i may have just lucked out but i don't
think i'm going to see anything oops
indexes
uh preference type remember yeah so
there's probably going to be a bunch of
member ids maybe not
uh well i'm sort of looking right but i
may have looked out that these had oh
and i wonder if these are actual names
but
in order to make it uh
unique typically what you would do is
like if i wanted to create an index on
the final field in contract
then you know i'd be create index
uh
contract
final
on
contract
final something like that and then it's
like again it's very easy when you look
at an index to figure out where it's at
so if you start
having issues if you have some sort of
index
error that comes back you know
particularly unique and stuff like that
you can tell oh it's on the contract
table i've got to go look at the final
column and i'll and actually i can look
in these indexes on this table
and i'll be able to see what that one is
and see why there's an issue with it
or
if you're try if your performance
going through some performance
improvements then you can see which
indexes are being used
for stored procedures and functions um
much like
functions and usually like you get
you're going to want to sort of mirror
probably the primary language that's
using it so
whether it's camel case or whether it's
lowercase or uppercase
a thing to remember with functions is
they're going to be
potentially used quite a bit within
queries
so you want you really want to avoid
like huge
obnoxious function name length
stored procedures you're going to like
you know you may be select you know star
from my big procedure name and then give
it some some parameters
the function may exist in 10 different
places because a function could be
things like uh force things to uppercase
so if i have to do that for five fields
in a table and my name my function name
is force all of the characters in this
two uppercase that's a long name and
it's really annoying to write that
several times
so you want it to be meaningful but
concise
and you might even do some uh it but you
got to be consistent maybe and do a
little bit of abbreviations and things
like that
just to make it a little easier to use
and again you're going to want to do
some sort of in the same vein as tables
with stored procedures and functions you
want to you know ideally
order the words within the name
so that they are grouped if they are
similars
for example with store procedures a lot
of times it makes sense to
start with the primary table name that
it works on
and then that will you know when you
sort them it will group all of those
together
not to mention just you know makes
things look a little better when it's
consistent
and you're going to run into the same
things whether you want them all lower
case or camel case or upper case
just
be consistent in how you do it
and then with parameters and variables
again
those are all over the place
as far as you know the companies and
organizations i've worked with so i
think it's just
pick something
uh some place it's just like variable
names in any other language sometimes
people want to precede them
uh have some sort of prefix that says
what type it is you know like all the
integers would start with a lowercase i
all the
strings would start with the lowercase s
or the var chars and you know may start
with a lowercase b things like that
um
just
find a way to be consistent or check
your you know if you're an organization
ask if they have a standards document
and
you know either go by that or if it's
missing some stuff if it has gaps don't
be afraid to say hey this there's a
couple things i have questions about why
don't we create standards for those as
well
now i i know i've gotten a little just
theoretical a little bit i guess in this
a little more academic but i think it's
important to think about these things as
we're getting into database development
in particular
so i think i'll just sort of uh stop
beating the dead horse now and wrap this
one up
and let you get back to your day so go
out there and have yourself a great day
a great week and we will talk to you
next time
you