Re: Newbie to RDBMS, Informix...E/R modeling question

David Carmean wrote: > > I'm trying to create a "contacts" database with SE V6.01 or so... > I'm new to Relational database theory, and to Informix as well. > I'm educating myself with the Informix manuals, and Date, C. J., > _An Introduction to Database Systems_, 1996. > > The Informix Tutorial suggests a need to resolve many-to-many > entity relationships with an intersection table. I'm having trouble > determining how to populate/update the database if the original > base relations no longer have foreign keys. > > For example, say I have the relations "name" and "address"; each name > can have multiple addresses of different types (postal,physical,home,etc.), > and each address can be associated with one or more names. > [snip] > BUT...if I do as suggested in the tutorial, and move the foreign keys > to a new base relation which is the intersection of the two original > relations, what technique can I use to populate the "address" table > then? How do I associate unordered records in the two original base > relations "name" and "address" with each other, when there are no longer > any foreign keys to use? I mean, how do I create/derive the > intersection when one of the tables is still empty? > > I must be missing some concepts still. If I understand your question, you can't. You have to populate the "parent" tables before you populate the intersection table. People must first populate the "Name" entity and "Address" entity, and then you can associate the two via your intersection entity. > C.J. Date apparently doesn't think much of the Entity/Relationship > model. I wasn't aware of this, but I think there are some pretty good reasons for not being fond of ER modeling. One of the things that can be difficult in ER can be deciding what is an entity and what is an attribute. For instance, in your example, I wonder if you really need an address entity at all. Here is the table: > create table address > ( > addr_idx serial, > orgname char(20), > mailstop char(20), > street char(20), > city char(20), > state char(2), > postal_code char(10), > country char(20), > addr_type char(10), > rec_num integer, > primary key (addr_idx), > foreign key (rec_num) references name (rec_num) > );> Set aside consideration of the "addr_type" attribute, and your surrogate key "addr_idx" for minute, and it looks to me like all the other columns are actually part of a large composite key that identifies a particular address. Now consider the "addr_type". I don't know about your business rules, but I doubt that "addr_type" is actually functionally dependent on address. For instance, many people run their business from their home, so the same address may have an addr-type of "Business" and addr_type of "Home". If this is the case, Address doesn't actually play any functional roles, so it probably doesn't need to be an entity. If you wanted to record the existence of gas storage tanks at an address, or the taxes leveied on an address, or anything specifically about an address, then you need an entity. You also might consider making a "Person" entity instead of a "Name" entity. You perhaps need another entity called "Organization", and then you can have the Orgnization represented as a foreign key in the Person entity. This way, you don't have to repeat the name of the organization (with all the attendant risks of mis-spelling, etc) as you would have to in the original table (below): > create table name > ( > rec_num serial, > orgname char(20), > lname char(20), > fname char(20), > middle char(20), > handle char(10), > name_type char(10), > primary key (rec_num) > ); Creating a seperate Organization entity would also allow you to record specific facts about the organization (e.g. phone number, organization type, etc). I think these types of considerations are much easier to address through a conceptual model, rather than a logical model (which is what most ER models are). The best conceptual modeling method that I know of is called Object Role Modeling (ORM). ORM is supported by a tool called Infomodeler, from Conquer Data in Bellevue WA. An ORM conceptual model is not some vague or ill-defined "broad brush picture". On the contrary, it is very detailed in that it contains every object and fact that the database must store, and every data (or most) oriented business rule that the database must enforce. The thing that makes conceptual modeling easier is that it allows you to work on understanding the business without rushing into table design. For instance, in an ORM model if you have a many to many relationship, you represent it that way. Once you have the conceptual model correct, you apply a mapping algorithim (which is automatic if you use a tool) to the model that automatically creates a properly normalized logical model. At the conceptual level, you don't treat entities and attributes differently. Everything is an object, and the entities and attributes "fall out" during the mapping process. ORM and the tool that supports it (Infomodeler) are neutral toward ultimate database target, but as an Informix user, you will be happy to know that Conquer Data has announced that they will fully support modeling for Informix's new Universal Server and datablades. With ORM you focus on the business data, and the correct table structure automatically follows. The best book on ORM is "Conceptual Schema & Relational Database Design" 2nd edition. By Dr. Terry Halpin, Prentice Hall Australia. ISBN 0-13-355702-2. You can get an overview of ORM by downloading the paper ORMVIEW.DOC from ftp://ftp.asymetrix.com/pub/infomodeler/Other This is an MS Word format paper that shows how to creat a small data model using ORM. The book goes into much greater detail, and is full of examples. I do not work for Conquer Data, but I have been using ORM (and teaching it in commercial classes) for the past 3 years and am convinced that it is the best way to design databases. Check it out, I think you'll like it. Thanks, Bill MacLean