Industry Standard Database Schemas

I am wondering whether there is a place where one can find sort of
industry standard/best practice database schmas for things that get
done repeatedly, say for examply the following:

CREATE TABLE employee_data
(
emp_id int unsigned not null auto_increment primary key,
f_name varchar(20),
l_name varchar(20),
title varchar(30),
age int,
yos int,
salary int,
perks int,
email varchar(60)
);

Here is a slighly different one:

CREATE TABLE employees (
name VARCHAR(50) NOT NULL,
telephone CHAR(10) NOT NULL,
position VARCHAR(20) NOT NULL,
salary DECIMAL(8,2) NOT NULL,
supervisor VARCHAR(50) NOT NULL
)

With small variations this just gets done over, and over, and over.

Wondering if there is any sort of central repository to help create
some uniformity.

Reporter wrote:
> I am wondering whether there is a place where one can find sort of
> industry standard/best practice database schmas for things that get
> done repeatedly, say for examply the following:
>
> CREATE TABLE employee_data
> (
> emp_id int unsigned not null auto_increment primary key,
> f_name varchar(20),
> l_name varchar(20),
> title varchar(30),
> age int,
> yos int,
> salary int,
> perks int,
> email varchar(60)
> );
>
> Here is a slighly different one:
>
> CREATE TABLE employees (
> name VARCHAR(50) NOT NULL,
> telephone CHAR(10) NOT NULL,
> position VARCHAR(20) NOT NULL,
> salary DECIMAL(8,2) NOT NULL,
> supervisor VARCHAR(50) NOT NULL
> )
>
> With small variations this just gets done over, and over, and over.
>
> Wondering if there is any sort of central repository to help create
> some uniformity.
>

Not really. There can't be.

For instance, if you're talking international phone numbers, you'll need
more than 10 characters. But if you're limiting to some countries, you
might want less than 10 characters.

Title might be a field - but it also might be an int, referring to
another table with possible titles in it. And so on.

In short, there is not going to be any one standard which will work for
everyone. There are too many variables.

However, I do have some scripts I start with and modify as necessary
when building tables. But those probably wouldn't work for you or
anyone else.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 1, 1:32 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Reporter wrote:
> > I am wondering whether there is a place where one can find sort of
> > industry standard/best practice database schmas for things that get
> > done repeatedly, say for examply the following:
>
> > CREATE TABLE employee_data
> > (
> > emp_id int unsigned not null auto_increment primary key,
> > f_name varchar(20),
> > l_name varchar(20),
> > title varchar(30),
> > age int,
> > yos int,
> > salary int,
> > perks int,
> > email varchar(60)
> > );
>
> > Here is a slighly different one:
>
> > CREATE TABLE employees (
> > name VARCHAR(50) NOT NULL,
> > telephone CHAR(10) NOT NULL,
> > position VARCHAR(20) NOT NULL,
> > salary DECIMAL(8,2) NOT NULL,
> > supervisor VARCHAR(50) NOT NULL
> > )
>
> > With small variations this just gets done over, and over, and over.
>
> > Wondering if there is any sort of central repository to help create
> > some uniformity.
>
> Not really. There can't be.
>
> For instance, if you're talking international phone numbers, you'll need
> more than 10 characters. But if you're limiting to some countries, you
> might want less than 10 characters.
>
> Title might be a field - but it also might be an int, referring to
> another table with possible titles in it. And so on.
>
> In short, there is not going to be any one standard which will work for
> everyone. There are too many variables.
>
> However, I do have some scripts I start with and modify as necessary
> when building tables. But those probably wouldn't work for you or
> anyone else.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Good points Jerry.

I still think it would be useful to have some prototypical starting
points. That would make it a lot easier to trade code around. For
instance I think it would be very useful to have standard field names,
field types, and sizes for the sort of information that goes in
employee and customer tables.

Why have a table called "Customers" and a table called "Customer_data"
that contain basically the same information? That just makes it more
difficult to trade code around.

Reporter wrote:
> On Jul 1, 1:32 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Reporter wrote:
>>> I am wondering whether there is a place where one can find sort of
>>> industry standard/best practice database schmas for things that get
>>> done repeatedly, say for examply the following:
>>> CREATE TABLE employee_data
>>> (
>>> emp_id int unsigned not null auto_increment primary key,
>>> f_name varchar(20),
>>> l_name varchar(20),
>>> title varchar(30),
>>> age int,
>>> yos int,
>>> salary int,
>>> perks int,
>>> email varchar(60)
>>> );
>>> Here is a slighly different one:
>>> CREATE TABLE employees (
>>> name VARCHAR(50) NOT NULL,
>>> telephone CHAR(10) NOT NULL,
>>> position VARCHAR(20) NOT NULL,
>>> salary DECIMAL(8,2) NOT NULL,
>>> supervisor VARCHAR(50) NOT NULL
>>> )
>>> With small variations this just gets done over, and over, and over.
>>> Wondering if there is any sort of central repository to help create
>>> some uniformity.
>> Not really. There can't be.
>>
>> For instance, if you're talking international phone numbers, you'll need
>> more than 10 characters. But if you're limiting to some countries, you
>> might want less than 10 characters.
>>
>> Title might be a field - but it also might be an int, referring to
>> another table with possible titles in it. And so on.
>>
>> In short, there is not going to be any one standard which will work for
>> everyone. There are too many variables.
>>
>> However, I do have some scripts I start with and modify as necessary
>> when building tables. But those probably wouldn't work for you or
>> anyone else.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================- Hide quoted text -
>>
>> - Show quoted text -
>
> Good points Jerry.
>
> I still think it would be useful to have some prototypical starting
> points. That would make it a lot easier to trade code around. For
> instance I think it would be very useful to have standard field names,
> field types, and sizes for the sort of information that goes in
> employee and customer tables.
>
> Why have a table called "Customers" and a table called "Customer_data"
> that contain basically the same information? That just makes it more
> difficult to trade code around.
>
>

Well, first of all I don't trade a lot of code around, so it's not a
problem. Not because I think my code is so great - but typically what I
write is pretty specialized. If the customer wanted a general purpose
solution, then he could get one quite easily. They call on me because
they have needs which can't be handled by a general purpose solution.

But that's off the subject. People have been trying to create standards
like you're proposing for over 20 years (that I know of, anyway). It
doesn't work because different systems have different needs - and
therefore different fields.

Customers, employees, whatever - the information required will change
with the needs of the customer.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 1, 2:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Reporter wrote:
> > On Jul 1, 1:32 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Reporter wrote:
> >>> I am wondering whether there is a place where one can find sort of
> >>> industry standard/best practice database schmas for things that get
> >>> done repeatedly, say for examply the following:
> >>> CREATE TABLE employee_data
> >>> (
> >>> emp_id int unsigned not null auto_increment primary key,
> >>> f_name varchar(20),
> >>> l_name varchar(20),
> >>> title varchar(30),
> >>> age int,
> >>> yos int,
> >>> salary int,
> >>> perks int,
> >>> email varchar(60)
> >>> );
> >>> Here is a slighly different one:
> >>> CREATE TABLE employees (
> >>> name VARCHAR(50) NOT NULL,
> >>> telephone CHAR(10) NOT NULL,
> >>> position VARCHAR(20) NOT NULL,
> >>> salary DECIMAL(8,2) NOT NULL,
> >>> supervisor VARCHAR(50) NOT NULL
> >>> )
> >>> With small variations this just gets done over, and over, and over.
> >>> Wondering if there is any sort of central repository to help create
> >>> some uniformity.
> >> Not really. There can't be.
>
> >> For instance, if you're talking international phone numbers, you'll need
> >> more than 10 characters. But if you're limiting to some countries, you
> >> might want less than 10 characters.
>
> >> Title might be a field - but it also might be an int, referring to
> >> another table with possible titles in it. And so on.
>
> >> In short, there is not going to be any one standard which will work for
> >> everyone. There are too many variables.
>
> >> However, I do have some scripts I start with and modify as necessary
> >> when building tables. But those probably wouldn't work for you or
> >> anyone else.
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================- Hide quoted text -
>
> >> - Show quoted text -
>
> > Good points Jerry.
>
> > I still think it would be useful to have some prototypical starting
> > points. That would make it a lot easier to trade code around. For
> > instance I think it would be very useful to have standard field names,
> > field types, and sizes for the sort of information that goes in
> > employee and customer tables.
>
> > Why have a table called "Customers" and a table called "Customer_data"
> > that contain basically the same information? That just makes it more
> > difficult to trade code around.
>
> Well, first of all I don't trade a lot of code around, so it's not a
> problem. Not because I think my code is so great - but typically what I
> write is pretty specialized. If the customer wanted a general purpose
> solution, then he could get one quite easily. They call on me because
> they have needs which can't be handled by a general purpose solution.
>
> But that's off the subject. People have been trying to create standards
> like you're proposing for over 20 years (that I know of, anyway). It
> doesn't work because different systems have different needs - and
> therefore different fields.
>
> Customers, employees, whatever - the information required will change
> with the needs of the customer.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Thanks for weighing in Jerry.

You say "People have been trying to create standards like you're
proposing for over 20 years (that I know of, anyway)".

Who?

I'd love to see what they've proposed.

It sounds like your needs are different from mine. I'm interested in
some pretty vanilla stuff.

Reporter wrote:
> On Jul 1, 2:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Reporter wrote:
>>> On Jul 1, 1:32 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> Reporter wrote:
>>>>> I am wondering whether there is a place where one can find sort of
>>>>> industry standard/best practice database schmas for things that get
>>>>> done repeatedly, say for examply the following:
>>>>> CREATE TABLE employee_data
>>>>> (
>>>>> emp_id int unsigned not null auto_increment primary key,
>>>>> f_name varchar(20),
>>>>> l_name varchar(20),
>>>>> title varchar(30),
>>>>> age int,
>>>>> yos int,
>>>>> salary int,
>>>>> perks int,
>>>>> email varchar(60)
>>>>> );
>>>>> Here is a slighly different one:
>>>>> CREATE TABLE employees (
>>>>> name VARCHAR(50) NOT NULL,
>>>>> telephone CHAR(10) NOT NULL,
>>>>> position VARCHAR(20) NOT NULL,
>>>>> salary DECIMAL(8,2) NOT NULL,
>>>>> supervisor VARCHAR(50) NOT NULL
>>>>> )
>>>>> With small variations this just gets done over, and over, and over.
>>>>> Wondering if there is any sort of central repository to help create
>>>>> some uniformity.
>>>> Not really. There can't be.
>>>> For instance, if you're talking international phone numbers, you'll need
>>>> more than 10 characters. But if you're limiting to some countries, you
>>>> might want less than 10 characters.
>>>> Title might be a field - but it also might be an int, referring to
>>>> another table with possible titles in it. And so on.
>>>> In short, there is not going to be any one standard which will work for
>>>> everyone. There are too many variables.
>>>> However, I do have some scripts I start with and modify as necessary
>>>> when building tables. But those probably wouldn't work for you or
>>>> anyone else.
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================- Hide quoted text -
>>>> - Show quoted text -
>>> Good points Jerry.
>>> I still think it would be useful to have some prototypical starting
>>> points. That would make it a lot easier to trade code around. For
>>> instance I think it would be very useful to have standard field names,
>>> field types, and sizes for the sort of information that goes in
>>> employee and customer tables.
>>> Why have a table called "Customers" and a table called "Customer_data"
>>> that contain basically the same information? That just makes it more
>>> difficult to trade code around.
>> Well, first of all I don't trade a lot of code around, so it's not a
>> problem. Not because I think my code is so great - but typically what I
>> write is pretty specialized. If the customer wanted a general purpose
>> solution, then he could get one quite easily. They call on me because
>> they have needs which can't be handled by a general purpose solution.
>>
>> But that's off the subject. People have been trying to create standards
>> like you're proposing for over 20 years (that I know of, anyway). It
>> doesn't work because different systems have different needs - and
>> therefore different fields.
>>
>> Customers, employees, whatever - the information required will change
>> with the needs of the customer.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks for weighing in Jerry.
>
> You say "People have been trying to create standards like you're
> proposing for over 20 years (that I know of, anyway)".
>
> Who?
>
> I'd love to see what they've proposed.
>
> It sounds like your needs are different from mine. I'm interested in
> some pretty vanilla stuff.
>
>
>

Thousands of people all over the world. I saw it for DB2 when I worked
for IBM; both IBMers and customers have tried standardizing fields, with
no success on any agreement. I saw it on usenet before it became a
commodity and was mostly military and educational organizations. And
since the internet became a commodity I've seen it all over the place.

I don't even pay attention to who does it any more - I've seen so many
different options. I'd suggest do some googling and or try some of the
generic database newsgroups.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 1, 3:16 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Reporter wrote:
> > On Jul 1, 2:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Reporter wrote:
> >>> On Jul 1, 1:32 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >>>> Reporter wrote:
> >>>>> I am wondering whether there is a place where one can find sort of
> >>>>> industry standard/best practice database schmas for things that get
> >>>>> done repeatedly, say for examply the following:
> >>>>> CREATE TABLE employee_data
> >>>>> (
> >>>>> emp_id int unsigned not null auto_increment primary key,
> >>>>> f_name varchar(20),
> >>>>> l_name varchar(20),
> >>>>> title varchar(30),
> >>>>> age int,
> >>>>> yos int,
> >>>>> salary int,
> >>>>> perks int,
> >>>>> email varchar(60)
> >>>>> );
> >>>>> Here is a slighly different one:
> >>>>> CREATE TABLE employees (
> >>>>> name VARCHAR(50) NOT NULL,
> >>>>> telephone CHAR(10) NOT NULL,
> >>>>> position VARCHAR(20) NOT NULL,
> >>>>> salary DECIMAL(8,2) NOT NULL,
> >>>>> supervisor VARCHAR(50) NOT NULL
> >>>>> )
> >>>>> With small variations this just gets done over, and over, and over.
> >>>>> Wondering if there is any sort of central repository to help create
> >>>>> some uniformity.
> >>>> Not really. There can't be.
> >>>> For instance, if you're talking international phone numbers, you'll need
> >>>> more than 10 characters. But if you're limiting to some countries, you
> >>>> might want less than 10 characters.
> >>>> Title might be a field - but it also might be an int, referring to
> >>>> another table with possible titles in it. And so on.
> >>>> In short, there is not going to be any one standard which will work for
> >>>> everyone. There are too many variables.
> >>>> However, I do have some scripts I start with and modify as necessary
> >>>> when building tables. But those probably wouldn't work for you or
> >>>> anyone else.
> >>>> --
> >>>> ==================
> >>>> Remove the "x" from my email address
> >>>> Jerry Stuckle
> >>>> JDS Computer Training Corp.
> >>>> jstuck...@attglobal.net
> >>>> ==================- Hide quoted text -
> >>>> - Show quoted text -
> >>> Good points Jerry.
> >>> I still think it would be useful to have some prototypical starting
> >>> points. That would make it a lot easier to trade code around. For
> >>> instance I think it would be very useful to have standard field names,
> >>> field types, and sizes for the sort of information that goes in
> >>> employee and customer tables.
> >>> Why have a table called "Customers" and a table called "Customer_data"
> >>> that contain basically the same information? That just makes it more
> >>> difficult to trade code around.
> >> Well, first of all I don't trade a lot of code around, so it's not a
> >> problem. Not because I think my code is so great - but typically what I
> >> write is pretty specialized. If the customer wanted a general purpose
> >> solution, then he could get one quite easily. They call on me because
> >> they have needs which can't be handled by a general purpose solution.
>
> >> But that's off the subject. People have been trying to create standards
> >> like you're proposing for over 20 years (that I know of, anyway). It
> >> doesn't work because different systems have different needs - and
> >> therefore different fields.
>
> >> Customers, employees, whatever - the information required will change
> >> with the needs of the customer.
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks for weighing in Jerry.
>
> > You say "People have been trying to create standards like you're
> > proposing for over 20 years (that I know of, anyway)".
>
> > Who?
>
> > I'd love to see what they've proposed.
>
> > It sounds like your needs are different from mine. I'm interested in
> > some pretty vanilla stuff.
>
> Thousands of people all over the world. I saw it for DB2 when I worked
> for IBM; both IBMers and customers have tried standardizing fields, with
> no success on any agreement. I saw it on usenet before it became a
> commodity and was mostly military and educational organizations. And
> since the internet became a commodity I've seen it all over the place.
>
> I don't even pay attention to who does it any more - I've seen so many
> different options. I'd suggest do some googling and or try some of the
> generic database newsgroups.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Ok Jerry thanks. You've been an enormous help.

Reporter wrote:
> On Jul 1, 3:16 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Reporter wrote:
>>> On Jul 1, 2:22 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> Reporter wrote:
>>>>> On Jul 1, 1:32 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>>>> Reporter wrote:
>>>>>>> I am wondering whether there is a place where one can find sort of
>>>>>>> industry standard/best practice database schmas for things that get
>>>>>>> done repeatedly, say for examply the following:
>>>>>>> CREATE TABLE employee_data
>>>>>>> (
>>>>>>> emp_id int unsigned not null auto_increment primary key,
>>>>>>> f_name varchar(20),
>>>>>>> l_name varchar(20),
>>>>>>> title varchar(30),
>>>>>>> age int,
>>>>>>> yos int,
>>>>>>> salary int,
>>>>>>> perks int,
>>>>>>> email varchar(60)
>>>>>>> );
>>>>>>> Here is a slighly different one:
>>>>>>> CREATE TABLE employees (
>>>>>>> name VARCHAR(50) NOT NULL,
>>>>>>> telephone CHAR(10) NOT NULL,
>>>>>>> position VARCHAR(20) NOT NULL,
>>>>>>> salary DECIMAL(8,2) NOT NULL,
>>>>>>> supervisor VARCHAR(50) NOT NULL
>>>>>>> )
>>>>>>> With small variations this just gets done over, and over, and over.
>>>>>>> Wondering if there is any sort of central repository to help create
>>>>>>> some uniformity.
>>>>>> Not really. There can't be.
>>>>>> For instance, if you're talking international phone numbers, you'll need
>>>>>> more than 10 characters. But if you're limiting to some countries, you
>>>>>> might want less than 10 characters.
>>>>>> Title might be a field - but it also might be an int, referring to
>>>>>> another table with possible titles in it. And so on.
>>>>>> In short, there is not going to be any one standard which will work for
>>>>>> everyone. There are too many variables.
>>>>>> However, I do have some scripts I start with and modify as necessary
>>>>>> when building tables. But those probably wouldn't work for you or
>>>>>> anyone else.
>>>>>> --
>>>>>> ==================
>>>>>> Remove the "x" from my email address
>>>>>> Jerry Stuckle
>>>>>> JDS Computer Training Corp.
>>>>>> jstuck...@attglobal.net
>>>>>> ==================- Hide quoted text -
>>>>>> - Show quoted text -
>>>>> Good points Jerry.
>>>>> I still think it would be useful to have some prototypical starting
>>>>> points. That would make it a lot easier to trade code around. For
>>>>> instance I think it would be very useful to have standard field names,
>>>>> field types, and sizes for the sort of information that goes in
>>>>> employee and customer tables.
>>>>> Why have a table called "Customers" and a table called "Customer_data"
>>>>> that contain basically the same information? That just makes it more
>>>>> difficult to trade code around.
>>>> Well, first of all I don't trade a lot of code around, so it's not a
>>>> problem. Not because I think my code is so great - but typically what I
>>>> write is pretty specialized. If the customer wanted a general purpose
>>>> solution, then he could get one quite easily. They call on me because
>>>> they have needs which can't be handled by a general purpose solution.
>>>> But that's off the subject. People have been trying to create standards
>>>> like you're proposing for over 20 years (that I know of, anyway). It
>>>> doesn't work because different systems have different needs - and
>>>> therefore different fields.
>>>> Customers, employees, whatever - the information required will change
>>>> with the needs of the customer.
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================- Hide quoted text -
>>>> - Show quoted text -
>>> Thanks for weighing in Jerry.
>>> You say "People have been trying to create standards like you're
>>> proposing for over 20 years (that I know of, anyway)".
>>> Who?
>>> I'd love to see what they've proposed.
>>> It sounds like your needs are different from mine. I'm interested in
>>> some pretty vanilla stuff.
>> Thousands of people all over the world. I saw it for DB2 when I worked
>> for IBM; both IBMers and customers have tried standardizing fields, with
>> no success on any agreement. I saw it on usenet before it became a
>> commodity and was mostly military and educational organizations. And
>> since the internet became a commodity I've seen it all over the place.
>>
>> I don't even pay attention to who does it any more - I've seen so many
>> different options. I'd suggest do some googling and or try some of the
>> generic database newsgroups.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================- Hide quoted text -
>>
>> - Show quoted text -
>
> Ok Jerry thanks. You've been an enormous help.
>
>
>

Thanks, but I don't feel like I've been much help at all in this case.
Not to turn you off the idea - but it's just something which hasn't
worked out for a long time.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 1, 5:27 pm, gordonb.sj...@burditt.org (Gordon Burditt) wrote:
> >I am wondering whether there is a place where one can find sort of
> >industry standard/best practice database schmas for things that get
> >done repeatedly, say for examply the following:
>
> You've left out *A LOT* of very necessary information here. Granted,
> in practice some of the information, even if it's based strictly
> on the employee, might be placed in separate tables with separate
> security restrictions. There's a group of employee information
> used for payroll and benefits. There's some for IT (e.g. the
> employee's password and access). There's some for HR / Equal
> Opportunity (that scam some governments call 'race').
>
> >CREATE TABLE employee_data
> >(
> >emp_id int unsigned not null auto_increment primary key,
> >f_name varchar(20),
> >l_name varchar(20),
>
> Check carefully the laws in your country about discriminating against
> a person based on the length of his name. Further, with any size
> company you will need middle name to distinguish between people,
> and even that will not always be enough (How many 'John Smith's do
> you have?)
>
> A name may need a prefix or title (Mr., Mrs., Miss, Ms., Dr., Queen,
> Lord, etc.) Some people get very touchy if you get it wrong.
>
> A name may need a suffix (2nd, 3rd, III, IV, Esquire, etc.) You
> need to be able to distinguish between the big boss and his brat
> son.
>
> >title varchar(30),
>
> 'Lord High Master of the Universe and DNS' won't fit in 30 characters.
>
> >age int,
>
> Age is a very awkward thing to put in a database record if it's not
> tied to a particular date. It changes, and you don't know when to
> update it. Date of birth, on the other hand, doesn't change much,
> (some people may have only an approximate idea of when it is, and
> then get more accurate information, so it COULD change) and you can
> calculate age from it.
>
> >yos int,
>
> I assume that means "Years of service". That is also a very awkward
> thing to put in a database record if it's not tied to a particular
> date. It's better to have a date here. You might think of calling
> it "date of hire", except it might differ from "date of hire" if
> you can leave, come back, and retain credit for previous service
> but not for time they were gone. So let's call it "service date".
> Today's date minus the service date gives you the number of years
> of service *IF* the employee is actively employed now. It changes
> only when you hire in (possibly again). Keeping credit for previous
> service may be a negotiable item in the hire-in process. This
> doesn't do a good job of tracking years-of-service when you're NOT
> working, but it may not matter then.
>
> >salary int,
>
> Depending on units involved, this may not *fit* in an int.
> Particularly if it's signed dollars per year where int is 16 bits.
>
> >perks int,
> >email varchar(60)
> >);
>
> Think of various applications for the database and the information
> they'll need.
>
> Company internal phone book (public information within company):
>
> name, work location, work phone, job title, work email, department
> Some companies include birthday (without year) as birthdays are
> recognized with some celebration like a note from management or a
> monthly cake for those having birthdays that month.
>
> Note: things like "work location" might refer to another table
> which details hundreds of offices scattered over the planet, and
> then a specific office at that location (e.g. Room 18, Floor 2 at
> Store #8291, where you look up Store #8291 to get what city, country,
> and address it's in). You also need to account for roving employees
> who show up at customer locations to service stuff and occasionally
> show up at one of several parts warehouses in a couple of states
> for replacement parts. Or maybe the parts are shipped to customer
> locations. They have no office or desk.
>
> Contact information:
>
> name, home phone number, cell phone number, home address, home
> email. (Note: an employee, or even most of your employees, can
> quickly lose functioning phone numbers, addresses, and email. Can
> you say "Hurricane Katrina"? Putting NOT NULL here is a bad idea.
> Also, some employees have no cell phone, and others have *only* a
> cell phone, and others have both.)
>
> Emergency contact information:
>
> name of emergency contact, contact's phone number, contact's address,
> contact's email, contact's relationship.
>
> Payroll (a lot of this gets USA-specific, but if you've got a
> multinational, you need all this AND more):
>
> name, home address (for mailing W-2), SSN, hourly vs. salary, pay
> rate, marital status, withholding exemptions (from W-4), payroll
> state (for state income taxes or lack thereof) and country, payroll
> city (for city income taxes or lack thereof). Note: SSNs are not
> guaranteed unique, even after you eliminate fraud and identity
> theft. So don't use them for an employee number.
>
> List of benefit plans employee has signed up for (health insurance,
> dental insurance, life insurance, disability insurance, 401K, pension
> plan, etc), deduction rate, and benefit level (e.g. health insurance
> for self vs. family).
>
> List of other deductions and deduction rate (union dues, payroll
> garnishment, payroll savings, charitable donations, etc.)
>
> Payroll direct deposit information: yes/no, bank routing and account
> information.
>
> Employee's current balance for sick days and vacation days. There
> may also be a current balance for an employee charge account (some
> employees can charge and pay via payroll deduction things like meals
> at the company cafeteria or merchandise at the company store.)
>
> You need some indication whether the employee is actively working
> (vs. retired, on unpaid leave, on military service, etc.) and should
> be paid. This needs effective start and stop dates.
>
> Note that pay rates are more complicated than an annual salary or
> an hourly rate, and especially that historical pay rates matter,
> especially to payroll. One's pay rate may change in the middle of
> a pay period. More commonly, a pay rate change is made effective
> as of a certain date (often the end of a pay period) but may be
> entered before then (or possibly somewhat after but before paychecks
> for that period are processed).
>
> Payroll needs some indication of where to send your paycheck (assuming
> there are work sites scattered all over the world).
>
> HR:
>
> HR needs information on skills for each employee, who reports to who,
> various job classifications (which might not show up in job title) and
> departments.
>
> HR needs information on your eligibility to work, which (in the USA)
> includes information on citizenship and work visa.
>
> HR needs information for the Equal Opportunity people, including a
> scam created by some governments called 'race'.
>
> IT and security information:
>
> Employee's access badge number, employee's computer password (probably
> stored encrypted), access rights to various systems, employee's
> work email account. Employee's parking pass number and license
> plate number.
>
> >Here is a slighly different one:
>
> >CREATE TABLE employees (
> > name VARCHAR(50) NOT NULL,
>
> Trying to use a name as a primary key is asking for trouble. It's
> not unique and people tend to spell it a lot of different ways for
> the same person.
>
> > telephone CHAR(10) NOT NULL,
>
> 10 characters for a (USA) telephone number has not been adequate
> since the invention of the extension. Nor does it cover dealing
> with multiple country codes. And not everyone has a telephone.
> And lots of people have several telephones.
>
> > position VARCHAR(20) NOT NULL,
> > salary DECIMAL(8,2) NOT NULL,
> > supervisor VARCHAR(50) NOT NULL
>
> Using a name as a primary key is asking for trouble. Consider what
> happens when one's supervisor gets married, and takes his/her
> spouse's name.
>
> >)
>
> >With small variations this just gets done over, and over, and over.
>
> >Wondering if there is any sort of central repository to help create
> >some uniformity.
>
> Companies that do payroll spend a lot of time keeping up with various
> state and federal laws in the USA relating to payroll and taxes.
> It's such a complex job that many companies outsource their payroll
> to companies that specialize in doing it. Their schema and database
> of tax rules for various locations is probably considered proprietary.

You sir, are the voice of experience. Thanks. That was very good
information. Here's the part I don't get however.

Why are the field names such as telephone, supervisor, position, and
salary not standarized. I am going to guess that your answer would be
they mean different things to different programmers in different
contexts.

>I am wondering whether there is a place where one can find sort of
>industry standard/best practice database schmas for things that get
>done repeatedly, say for examply the following:

http://www.databaseanswers.org/data_models/index.htm