Unique usernames in 2 tables at different levels. Suitable database design? -

i have table suppliers. table contains, among other things, name, username , password hash.

sometimes supplier 1 person, in case works fine. supplier company , in case, multiple employees may need log in. until now, supplier employees shared 1 username/password per supplier. want give individual credentials.

i have table supplier contacts, easiest add 2 columns username , password hash table. however, since want keep option of having usernames , password hashes in supplier table one-person suppliers, can't create unique index usernames, because they're spread out on 2 tables.

i of course enforce unique usernames in php, wonder if i'm making database design mistake. considered creating new table 'users' put credentials in somehow doesn't feel right. tips?

i think both david aldridge , ray more have given potentially suitable design suggestions, i'd answer question little more holistically, , concentration on statement "[you] wonder if [you're] making database design mistake."

---

short version: yes, think are making design mistake - insisting on putting single-person supplier login details in supplier table - , causing reject both own alternative ideas, , suggested others.

long version: first, think conceptually, rather technically. login details belong to? in past, belonged supplier. now, belong supplier employee.

earlier in data modelling process (i.e. before reach point of creating physical model), talk attributes rather columns, , entities rather tables - login details attributes of supplier employee entity rather attributes of supplier entity. means when reach physical database design, columns should in (or associated with) suppliercontact table.

so why logins belong supplier employee rather supplier? think scenario:

you work supplier has single employee, create 1 set of login details them. year later, supplier's business expands, , person running hires assistant - need access, need make set of login details same supplier.

with model you're suggesting, you'd need delete current login details supplier table, , add 2 sets of details suppliercontact. depending on application design, might find needing close supplier's original account or forcing reset of password - irritating them.

---

alternative situation: if have legitimate reason having 2 different types of account - organisational account suppliers , individual account supplier employees - it's different situation. however, still achieve both goals of having accounts in 1 table while still associating each type of account relevant table (i.e. supplier or suppliercontact).

for example, have table called account held login details. there, there several options in order link table either supplier or suppliercontact. don't want option choose issue that's well-discussed elsewhere (including on so), , arguably matter of taste, here options:

• include 2 nullable foreign key columns in account, 1 can hold supplierid, , 1 can hold suppliercontactid. potentially build constraint ensure 1 of 2 populated.
• create 2 separate tables, supplieraccount , suppliercontactaccount, 2 columns each. first should have supplierid , accountid columns foreign keys supplier , account, , second should have suppliercontactid , accountid columns foreign keys suppliercontact , account. potentially build constraints ensure rows in account not being associated more 1 supplier or suppliercontact.

please note would not above unless had 2 different types of account. it's added complexity, , should not done unless needed; if reason keeping details associated supplier used structure or prefer way, don't it.

---

another alternative: had thought on in meantime. i'll not go heaps of detail you've accepted , presumably know enough move on, option use party data modelling pattern.

this pattern can dealing situations need interact in similar ways both individuals , organisations or groups of people. it's useful situations 1 dennis has mentioned, might interact individual or organisation in multiple different ways - i.e. party might play more 1 role.

here's article idea here found useful when first ran across it. len silverston, wrote article, has published helpful data modelling books if want more in-depth. books work more conceptual/abstract level right down physical database schemas. if around online party data model pattern i'm sure can find similar resources online, if useful situation.