Professional Web Applications Themes

Industry Standard Database Schemas - MySQL

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, ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Industry Standard Database Schemas

    Reporter wrote: 

    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.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Industry Standard Database Schemas

    On Jul 1, 1:32 pm, Jerry Stuckle <net> wrote: 





    >
    > 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.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    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 Guest

  4. #4

    Default Re: Industry Standard Database Schemas

    Reporter wrote: 
    >> 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.
    >> net
    >> ==================- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > 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.
    >
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Industry Standard Database Schemas

    On Jul 1, 2:22 pm, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > 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.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    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 Guest

  6. #6

    Default Re: Industry Standard Database Schemas

    Reporter wrote: 
    >> 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.
    >> net
    >> ==================- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > 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.
    >
    >
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Industry Standard Database Schemas

    On Jul 1, 3:16 pm, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > 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.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

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



    Reporter Guest

  8. #8

    Default Re: Industry Standard Database Schemas

    Reporter wrote: 
    >> 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.
    >> net
    >> ==================- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > Ok Jerry thanks. You've been an enormous help.
    >
    >
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: Industry Standard Database Schemas

    >I am wondering whether there is a place where one can find sort of 

    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').
     

    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.
     

    'Lord High Master of the Universe and DNS' won't fit in 30 characters.
     

    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.
     

    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.
     

    Depending on units involved, this may not *fit* in an int.
    Particularly if it's signed dollars per year where int is 16 bits.
     

    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.

     

    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.
     

    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.

     

    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.
     

    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.

    Gordon Guest

  10. #10

    Default Re: Industry Standard Database Schemas

    On Jul 1, 5:27 pm, org (Gordon Burditt) wrote: 
    >
    > 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').

    >
    > 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.

    >
    > 'Lord High Master of the Universe and DNS' won't fit in 30 characters.

    >
    > 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.

    >
    > 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.

    >
    > Depending on units involved, this may not *fit* in an int.
    > Particularly if it's signed dollars per year where int is 16 bits.

    >
    > 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.


    >
    > 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.

    >
    > 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.

    >
    > 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.



    >
    > 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.[/ref]

    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.


    Reporter Guest

  11. #11

    Default Re: Industry Standard Database Schemas

    >I am wondering whether there is a place where one can find sort of 

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

    Charles Guest

  12. #12

    Default Re: Industry Standard Database Schemas

    Charles Polisher wrote: 
    >
    > http://www.databaseanswers.org/data_models/index.htm
    >[/ref]

    One person's ideas of some models. They're good. But they won't work
    in 98% of the systems I program.

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

  13. #13

    Default Re: Industry Standard Database Schemas

    On Jul 1, 8:07 pm, Jerry Stuckle <net> wrote: [/ref]

    >
    > One person's ideas of some models. They're good. But they won't work
    > in 98% of the systems I program.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Charles, thank you very much. Those are super helpful to me.

    Reporter Guest

  14. #14

    Default Re: Industry Standard Database Schemas

    >You sir, are the voice of experience. Thanks. That was very good 

    Because they do not have a standardized meaning. Are you familiar
    with the distinction between a salaried employee and an hourly
    employee, for example?

    Why should they? Payroll programs are, for the most part,
    closed-source. (Do you know of any exceptions, ones good enough
    to be in actual use for a 10,000-employee company with offices in
    multiple states?) Also, people are going to be *VERY* hesitant to
    use add-on programs that interface directly to their payroll system.
    There's all kinds of laws about accounting accuracy and financial
    disclosure that makes such an interface very risky. You don't get
    any benefit from common field names, except perhaps for people
    trying to merge two companies using different payroll systems. And
    there's probably an industry developed around that, and they don't
    have anything to gain and lots to lose from common code that would
    cost them their jobs.


    What does "telephone" as a field name *mean*? An employee might
    reasonably have several of them: home hardwired phone, home cell
    phone, work phone, work cellphone, work pager, home fax, work fax,
    etc. Even my Palm knows about various kinds of phone numbers.
    There are real distinctions here, as most employees won't have a
    work number and a home number that are the same (except perhaps for
    military living on a military base).

    What should be the contents of the field "telephone"? For some
    companies, a 4-digit extension number is enough (assuming it means
    "office phone"). For many, a 10-digit USA phone number is OK.
    Others need an extension also. Some need international phone
    numbers.

    What should be *in* the field named "supervisor"? An employee ID?
    A position ID? A name? Things can get messy if an employee reports
    to a *position*, not a *person*, and a *person* can occupy several
    positions at once.

    What exactly *is* a "position"? Is it "Assistant Manager"? Or
    "Assistant Manager 3rd shift of Store #81743"?

    A salesman gets a base salary of $20,000 a year plus 10% commission
    on all sales after the first $50,000 and gets follow-on bonuses for
    new customers for subsequent sales on sliding scale A1, plus a
    year-end bonus according to schedule B7. What goes in the "salary"
    field, and what type is it? What currency is it in?


     

    No, it's that the entities you describe mean different things to
    different *PEOPLE* (not programmers, users, such as the management
    and employees of a company) in different industries.


    It probably isn't that difficult for a programmer to look at and
    understand an application written in a programming language he's
    never seen before. (Mr. Spock of Star Trek might have no trouble
    understanding ancient languages like C. I have managed to spot a
    bug in some Python code without ever having seen Python or knowing
    that's what it was, and that's not a particularly difficult
    accomplishment.)

    It will be extremely difficult for a programmer to look at and
    understand an application requiring application-domain knowledge
    that the programmer doesn't have. For example, if he's trying to
    write a payroll system, it would be a darned good idea if he knew
    what currency is (Mr. Spock of Star Trek might have trouble with
    this if he didn't realize what the application was doing), what a
    tax is, and some knowledge of laws regarding payroll. Similarly,
    designing rocket guidance software requires that you know a little
    something about rockets and the physics behind guiding them.

    Gordon Guest

  15. #15

    Default Re: Industry Standard Database Schemas

    Anyone know what http message a browser sends to a server when the
    server sends the browser a a WWW-Authenticate header and the user
    cancels the User/Password dialog that the browser pops up?

    Reporter Guest

  16. #16

    Default Re: Industry Standard Database Schemas

    Reporter wrote: 

    Try alt.html for a start. This has nothing to do with MySQL.

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

  17. #17

    Default Re: Industry Standard Database Schemas

    On Jul 1, 8:50 pm, Jerry Stuckle <net> wrote: 
    >
    > Try alt.html for a start. This has nothing to do with MySQL.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Thank you

    Reporter Guest

  18. #18

    Default Re: Industry Standard Database Schemas

    On Mon, 02 Jul 2007 02:39:57 -0000, org (Gordon
    Burditt) wrote:
     

    And thus more philosophically, because reality doesn't fit in a grid.

    Back from Aristotle with his Categories, until Kant who said the
    problem was easy to solve (and in consequence put it apart) but not
    solved yet, the starting point to talk about something is to
    categorize it : if you want to talk about anything, you must
    categorize everything.

    There are also linguistical considerations, the basic example being :
    "snow" is a word very hard to translate into Inuits language because
    it is far too general for their every-day life - what kind of snow do
    you talk about exactly ? - and translating these specific but basical
    Inuits words for snow into English would require all the very
    technical terms for skiing, which are not accessible to 99% of the
    English speaking population.

    If you want to be systematic, you'll have extra-work and you'll miss
    something at the end anyways. You can have guidelines, but they will
    be given by the context of your practical application - the key point
    is not what you're looking at, but how the watcher will interpret it,
    according to her culture (in a wide acception).
    subtenante Guest

Similar Threads

  1. Replies: 3
    Last Post: July 25th, 12:19 PM
  2. cleanup of pg_temp schemas
    By Marcel Gsteiger in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 26th, 03:58 PM
  3. Schemas they say ...
    By HG in forum ASP.NET Web Services
    Replies: 7
    Last Post: November 18th, 10:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139