What is the easiest way to workout a one-to-one, one-to-many, mandatory-one-to-many and optional-one-to-many etc from a database?

If the database was well designed, the constraints will tell all. Consider 2 tables A and B:

• optional one-to-one: B has a foreign key F to A and there is a unique constraint on the columns of F and the columns of F allow NULLs.
• mandatory one-to-one: B has a foreign key F to A and there is a unique constraint on the columns of F and the columns of F do not allow NULLs.
• optional one-to-many: B has a foreign key F to A and there is no unique constraint on the columns of F and the columns of F allow NULLs.
• mandatory one-to-many: B has a foreign key F to A and there is no unique constraint on the columns of F and the columns of F do not allow NULLs.

A good CASE tool could reverse engineer the entity model from such a database.

If it was not well designed, you will have to infer the relationships from the data, and verify them with a knowledgeable user (e.g. "it appears that each FOO may have 0 or many BARs - is that correct?")

So if the table Customer has a primary key of cusno
and the Order table has a primary key of orderno

The Order table has a foriegn key of customer.cusno what does it make the relationship? There are no constraints supplied?

Clearly one customer may have many orders, and each order is for exactly one customer.

Clearly ?!?!

-PatP

Well, I thought so!

It is clearly only if you are sad like me and spend all day doing databases.

For normal people it might be an idea to toy with a good diagram method (ERD-Object Modling methods for example) that let you "see" the relationships.

What about joint-purchase covenants, which prohibit a single legal entity (person, corporation, etc) from owning property? That would require multiple customers for a single invoice.

I guess the point that I was trying to make is that we as data modelers can't assume anything until it is verified by the problem-domain experts (the end users).

-PatP

My assessment was based on the information given that "The Order table has a foriegn key of customer.cusno what does it make the relationship?"

You can only put one customer in a row in the Order table, which means the order hasonly 1 customer.

Remember, this question was about working backwards from the database to the ERD...

Ah, good point. I should have re-read the whole thread. Sorry.

-PatP

Thanks for the great answers, but what about a many-to-many relationship, how do you calculate this type?

A many-to-many relationship would be implemented by an "intersection table" like this one (between employee and project):

create table emp_project
( emp_id references employee
, proj_id references project
, ...
, constraint emp_project_pk primary key (emp_id, proj_id)
);

but with a picture

This is a many to many relationship worked out in a database

This is simply a picture of what andrewst is saying.