just a theory question.

Say I had a login screen which takes username and password. but i have two tables, customers and employees (both can log in)

I know its easy to check both tables for the correct combo...but:

How would I (using fk) make sure the same username was not in both tables? Is this the best design or is it better to have a table called "person" which stores everybody. It would have an extra column to store wheather the person was an employee or a customer?

thanks

"How would I (using fk) make sure the same username was not in both tables?"

you can't

"Is this the best design or is it better to have a table called "person" which stores everybody. It would have an extra column to store wheather the person was an employee or a customer?"

that is better

it is called supertype/subtype

Would the pk of the supertype table person bee the pk + fk of the subtype table?

What would be the correct naming convention for this setup?

person
person_employee
person_customer


Which brings me to my other main "theory" question..In your expereince what is the best naming convention for table and column names?

id or ID or PersonID or person_id

I know these are trivial questions but I would rather get it right the first time...

"Would the pk of the supertype table person bee the pk + fk of the subtype table?"

yes, that's right

there is no "correct" naming convention

the best naming convention, in my opinion, is

- lean
- short
- evocative

lean means don't throw crap into the name, like tblPeople for the people table

short should be obvious, you will be typing the name, and the fewer keystrokes the better

evocative to give an indication of what's in the table/column, so BackorderAmount instead of BOAmount (shorter but misleading) or -- $deity help us -- Amount (meaningless)

it should also go without saying that you should not name a column with a reserved word like Date, but i see this all the time

there is a bit of controversy surrounding the naming of primary keys

if table1 has a pk called ID, and table2 is related to table1 with a foreign key, what do you call the foreign key?

most people (myself included) would call it table1ID, but this prevents you from using the USING keyword in joins, and the NATURAL join altogether

in order to use USING or a NATURAL join, you would have to go back to table1 and name the primary key table1ID, which i find a bit redundant

so a column can be a pk and a fk at the same time

examle:

person:
personid | name
5 | jon

employee:
personId | officeID
5 | 10

employee.personId is a pk and a fk, if not how do you stop a double entry:

employee:
personId | officeID
5 | 10
5 | 10

yes, personID in the employee table is both pk in its own table and fk to the people table

In your experiences, is it normal to have a supertype/subtype relationship where employees and customers can be stored in one supertype table. I am just trying to think of any problems that can arise in the future where it might need to be seperated.

It seems that a database designer can "over-think" a database design....

normal?

i dunno

it depends on so many things