Database Design

Consider the following database design concepts:  foreign key, relationship types (1:1, 1:M, M:N), connectivity, cardinality, strong entity, weak entity, strong (identifying) relationship, weak (non-identifying) relationship, optional participation, manual participation.

I understand what all these concepts are and what is said to be important about them.  But I just do not understand why they are important.  I presume it has something to do with the actual creation of the database, i.e., the DDL used to create the database objects.  For example, does it have something to do with the order of table creation?

Or, perhaps the question is better posed as this:  Once I have rendered a complete ERD (replete with all the concepts above), then how is the importance of all those concepts translated?

Oh wait, just thought of something.  Please refer to the embedded ERD.  How is the importance of the concepts translated?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You example design can be further normalized through the use of state/city/zip in a separate reference table to which customer and vendor can refer.

The idea is to maximize information while minimizing the amount of storage. The issue though is that there is a tradeoff 100% normalizing means that there are many queries that need to include other table references.

Your queries joins, etc will be used to assemble the  data for presentation/display.
Why these concepts are important? You may obtain tons of reasons...

If you understand we need to ensure data integrity then these concepts are also important to define the framework and a set of rules which are universal and which we all may understand. Everybody knows what PK and FK mean thus we may understand systems created by other developers and designers and ensure the continuation and future improvements. The data integrity can be ensured by implementing other concepts but such system is hard to maintain, improve, and fix possible errors.

And this is not valid for database design only. When you define new programming language using e.g. BNF then you don't need to write a compiler because automated tools will do it for you. etc.

Of course, this does not mean concepts mentioned by you are final and the best ones... When computers and their software will come closer to the human brain then the PK/FK importance will become lower and data storage and searching will be much different than our today's simple concepts.

Your ERD uses some of the concepts mentioned but it still does not explain everything needed to understand or implement it. If you allow No vendor for the product then OK. But how do you recognize product which is not in the product table? Is the order_item sufficient for the order line identification? Etc. Etc.

The importance is in the fact I can ask such question because you've used concepts which are common to all database applications and everybody understands them.

BTW, why didn't you mention data normalization in your question which itself needs PKs, FKs, relations etc. etc. ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David AndersTechnician Commented:
This is Filemaker specific, Filemaker novices have very little database background. It is well written.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PeterAuthor Commented:
Okay...your replies are helpful, but I'm still confused.  Maybe I'm not asking the question right.  I think pcelba comes close when he says the "ERD uses some of the concepts mentioned but it still does not explain everything needed to... implement it.

I'm using SQL Server.  By implementation you mean creating the actual database with the data contained within the completed ERD (and the data dictionary), correct?  

So, during implementation, what am I doing that reflects the importance of understanding those concepts?

For example,
1.  The cardinality of Customers to Orders is 1:M.  Does that mean, that during implementation, I must create the customer table first?
2.  During implementation, how is the importance of participation reflected?  Why, during implementation, do I care what the participation is?

BTW - and this may be another clue - in response to pcelba again, I purposely did not mention normalization because I understand it apart from implementation.  That is, in my view, normalization is not implemented; it is a process completed before implementation.
1) yes.
Before creating an order, the customer must exist.

Normalization is a means by which you have one record to maintain that can be used in various ways.
Case in point, one usually will have a map city, state, zip. street names, etc.

In you db design, customer and vendor each gave city, state, zip
Normalization of those could reduce the storage needs of 32 char for city, 15 char for state with an int for each.

Db designers/architects pure approach deals with minimizing repetitive information in different columns/records.
The implementation of the DB is not done in a vacuum meaning, the DB U.S. Designed/payed out, and the app architect/developer/programmer has to conform....

There is a give and take between the competing groups. That happens before the User ease of use group gets involved.
All these rules are just helping to implement your business model. And the implementation means tables and data creation for me.

Customers to Orders are 1:M in your case (and in many other cases) but we can imagine N:M.  Two customers can order one house, one car, one Yakuzzi, one bottle of coke etc.  And you don't need to create the Customers table prior to the Orders table. You don't even need to create Customers record prior to the Orders record (which is not obvious) but your model says "each Order must point to one Customer in the final state". All intermediate states can occur in data but such data do not follow given rules.

Your database design should follow your business requirements and then some rules given by the "state of the art". OTOH, the database modelling rules will avoid problems when converting your business requirements into the "machine language".

And you don't necessarily need to implement your requirements in relational database...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.