Re: Schema Anomalies

DM Unseen wrote:
> Some foreign keys column names can be tricky.
>
> given a table Contact with a PK ContactID and a Investor's table that
> has a MTO relation to table Contact
>
> The foreign key could look like this:
>
> "Investor ContactID" ->Contact.ContactID
> or
> Investor2Contact -> Contact.ContactID
> or
> Investor_ContactID -> Contact.ContactID
> or
> InvestorContactID -> Contact.ContactID

I'm not exactly sure what you're trying to say here. Is this in
reference to the implied relationships anomaly? If so then you are
correct that it would not detect these relationships, but I don't think
that there would be a way to detect them if there's no foreign key
defined. Note that the implied relationships anomaly was the first one
that I added. I've seen this problem in several production databases
where someone just forgot to add the foreign key.

> Tables that contain a single column are OK provided there is a unique
> constraint/index/PK on the column.

Any of these things might be OK for a given schema. In one of our
schemas they were using a separate single column table for each type to
identify type information. This questionable design led to some
extremely nasty SQL.

What would be a valid/appropriate use of a single column table?

> A good addition would be relation analisys: so deriving OTM/MTO and
> OTOP/OTOF relations from the actual schema.

Yes, I've thought about adding that, but am unsure as to how to present
that information. I'll have to experiment with that a bit.

> Also recognizing
> intersection tables (MTM) would be very nice(intersection tables only
> consist of foreign keys, although some of them might even have a unique
> index/constraint)

That shouldn't be too difficult. Which term, intersection table or
junction table, is more accepted/appropriate?

Thanks,
John Currier
http://schemaspy.sourceforge.net

.

Relevant Pages

  • Re: circular relationships ok?
    ... In other words, a database schema consisting of the relation schemata, ... and the foreign key constraints, ...
    (comp.databases.theory)
  • Re: Foreign key constraints from DB at runtime
    ... OleDbConnection object, and then use the GetOleDbSchemaTablemethod to get ... This will get you all the foreign key definitions inside the schemaTable ... I'm trying to detect them from the database's schema. ... foreign key constraints to other tables. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Business objects, subset of collection
    ... SQL works on not normalised data ... foreign key (vehicleid) references vehicle ... And who did the poor schema design, ...
    (comp.object)
  • Re: Diagrams
    ... How can I setup a foreign key while I'm designing a tabel??? ... > They are a simple ERD tool for visualising the schema of your database. ...
    (microsoft.public.sqlserver.server)
  • How to link foreign keys & primary keys using python?
    ... # schema defined earlier) ... school_fk = HashedFK(name = ' School code FK', ... An index is formed on the field AREACODE in the ... then the record is not validated(as foreign key constraint ...
    (comp.lang.python)