ideas on resolving one-to-one relationships?

pdaoust

Hi. I'm trying to design a POS/inventory/eCommerce website right now, and I've got as far as designing the data structures (i.e., the tables). But I've run into an annoying problem. I have two tables that (IMO) shouldn't be merged into one table, but I'm not really familiar with the ins and outs of the incredibly rare "one-to-one" relationship.

Here are the two tables:

person
uid -- primary key
firstName
lastName
company
address1
address2
city
province
postalCode
country
phone
fax
email
notes

webUser
uid -- primary key
userName
password
email
firstName
lastName

As you can see, there's a lot of redundancy, which I'm not happy with. The e-mail field is repeated, as well as the first and last names.

So why don't I just join the two tables together, you ask? Well, not all people in the database have web sign-ins (in the case of a customer whose info is entered in a POS terminal in the store), and not all web users have mailing/contact info (in the case of administrators or store clerks, who still need to log in so they can have access to the admin pages).

What do you think? I'm stumped. And what table would I put the foreign key on? Would it matter? Should I creat a junction table, or is that only for many-to-many relationships?

TIA,
Paul d'Aoust

tomhath

You can still put them together, just leave the fields NULL if you don't have the info.

pdaoust

That was my first thought, but IMO "web user" and "customer" are distinct enough entities that they should be separated.

[deleted]

"That was my first thought, but IMO "web user" and "customer" are distinct enough entities that they should be separated."

Exactly how are these two related?

pdaoust

How are they related? When a person is browsing the store and decides to create an account, they'll probably want to enter shipping/mailing information (hence the fields in the "person" table). This account will of course include access privileges to pages like "my shopping cart" et al.

That's how the two are related. Now here's how they're unrelated: not all users of the website will need to enter shipping information; i.e., shipper/receivers and cashiers using the POS end of the program will need web accounts (for access privileges to deeper levels of the site), but obviously won't need to enter their own mailing address, etc. So that's how you can have web users without having an entry in the "person" table. (A better for the "person" table might be "client" or "customer".)

On the flip side of the coin, a cashier may be filling out an invoice for a person, entering their name/phone number/address/etc, but that customer obviously won't need a user account on the website, because they didn't purchase anything through the website.

Maybe I should elaborate on that last point a little: This isn't just a webstore I'm building but also an inventory management/POS program that will be used inside a store with a physical location.

jeremuck

you can put them in the same table. no biggy. you don't have to make all the fields required.

anyway, since you seem extremely opposed to that,
you could have a table for admins and another table for customers. Those are two very different types of people.

or you might want to enable customer's to have more than one contact information? (what if they want to deliver to different addresses or billing address different than delivery address).

you might have a table describing accounts (store, web, admin), and another table describing the people owning the accounts, and yet another table describing contact information.

why are addresses -> person a one to one relationship?

and why are people -> addresses (which right now is the same as people -> account) a one to one? a husband and wife are two people... they probably want one account. (like Blockbuster you can have the whole family rent on same account).

I hope I gave you some ideas.

pdaoust

hmmmmmmmmmmmmmmmmm.... you gave me something to chew on; you have a lot of good points in your post. The idea of separating people from their addresses is an especially interesting one. Or else maybe I should just merge the tables. It's not like there will be very many cashiers/shipper-receivers.

Thanks for the thoughts, guys! I'll try to figure something out.

[deleted]

So the bottom line really is that you have customers and suppliers, and suppliers don't use some of the properties that cusomters use.

Maybe you should seperate the customers from the non-customers instead?

After all, each and every customer needs the same properties, even though they don't all fill them out. The only difference between a web-customer and areal-customer is that the web-customer has a logon name and password, and the real customer doesn't, so you can leave that blank for the real costomer.

michael

It might be faster to do two seperate tables...

I think you should merge the tables and have a seperate int(1) to specify if they're a webuser. If they are, use a foreign key to another table where the unique values of the webuser are located. That way you don't double up data.

Michael

[deleted]

"It might be faster to do two seperate tables..."

How do you figure?

michael

If all you need is the web user data distinct from the other...

Such as cases where login, password is in the webuser table, you wouldn't have to search through all the crap in the main table.

jeremuck

OSCOMMERCE project has an interesting database scheme

(sourceforge.com or http://www.oscommerce.com/ )

[deleted]

"Such as cases where login, password is in the webuser table, you wouldn't have to search through all the crap in the main table."

'all the crap' is fully indexed, so I don't think you're going to notice any slowdowns. from saerching records that have NULL inserted for the webuser data...

What will notice is that adding a customer and updating a customer becomes much more complex because you have to work with two tables instead of one.

The real issue is: is there a real one-to-one relation between cusomters and webusers (and as far as I can tell there is) because then it makes no sense at all to seperate them.

michael

I failed to read the part where he mentioned it was indexed.

My apologies. =)

[deleted]

"I failed to read the part where he mentioned it was indexed.

My apologies. =)"

well, he didn't really, but it stands to reason that those columns that are to be searched will be indexed 🙂

pdaoust

hmmmmmmmmmmmmmmmmmm..... using indexes... I never thought of that, primarily because the first time I learned about indexes was way back in my hacking youth, when I didn't know much about hashes and stuff. So I just disregarded the whole issue. Maybe I should go back and read that part of the MySQL manual, eh? 😉