The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

In SQL can a table be defined as auxiliary?

In SQL can a table be defined as auxiliary / mapping table?

Is there a keyword for that?

Thanks.
Q and Wait
Tuesday, August 14, 2007
 
 
The SQL standards do not define such a thing (AFAIK)...

did you mean Microsoft SQL Server?
Totally Agreeing
Wednesday, August 15, 2007
 
 
what do you mean by auxiliary / mapping table?
Totally Agreeing
Wednesday, August 15, 2007
 
 
Hello, I meant the SQL language, but the concrete implementation I'm using is MYSQL - if it's possible in others I also would like to know.
What I mean by auxiliary / mapping table is a table that you use to store FK that relate other tables in a many to many relation. Thanks.
Q and Wait
Wednesday, August 15, 2007
 
 
I think this is a matter of naming conventions, not a metadata thing that you can set at the database level, at least not in SQL Server.

I have seen people call these 'relationship' tables, and putting some prefix in the name to let everyone know that they reference 'entity' tables.
Sergei
Wednesday, August 15, 2007
 
 
If I understand what you're looking for, then as far as I know, no. It's just a regular ordinary table with a pair of foreign keys in it.

Personally, I recommend against defining a special naming convention to distinguish these tables from the others. I don't have any mapping/linking/relationship tables that have only the pair of foreign keys. In every case, there is something about the relation that needs to be described (i.e. additional fields) and that means that the table really is just like every other table.

For example, if one examines the relationship between customer/order and inventory, you'll find that (in general) each order can contain mulitple items and that each item can be on any number of orders. Thus, one ends up needing a linking table. As it happens, it's simply the ubiquitous OrderDetail table containing both OrderID and ItemID as foreign keys PLUS a whole lot more (quantity ordered, volume discount, etc.).

In my experience, these kinds of tables are simply artefacts of the standard analysis and normalisation practices and there is rarely, if ever, a need to actually sit down and say "I need a linking table." I think that the emphasis that some courses place on dealing with the 'difficulty' of a many-to-many relation is misguided. What's tough or unusual about a table containing more than one foreign key?
Ron Porter Send private email
Wednesday, August 15, 2007
 
 
The SQL standard lets you indicate what table and field a foreign key references in a CREATE TABLE statement using a foreign key constraint. Last time I used MySQL, however, it didn't use or maintain that information (it accepted the syntax but discarded the data).

The SQL syntax looks something like this:

create table PERSON_PHONE (
  PERSON_ID integer not null,
  PHONE_ID integer not null,
  foreign key (PERSON_ID) references PERSON.ID,
  foreign key (PHONE_ID) references PHONE.ID
)
Jeffrey Dutky Send private email
Wednesday, August 15, 2007
 
 
"Thus, one ends up needing a linking table. As it happens, it's simply the ubiquitous OrderDetail table containing both OrderID and ItemID as foreign keys PLUS a whole lot more (quantity ordered, volume discount, etc.)."

I understand what you are saying, but in that case I wouldn't call it a mapping table because that table actually serves a purpose other than mapping M-M relations.

What I call Mapping tables usually just contain the PK and maybe some date field when the PK1 + PK2 is not unique, for example:

Car rental - the same person could rent the same car twice or more, so the Rent table could be

PersonCar (Car, Person, RentDate)

Where Car and Person are the PK fields of the respective table.
Q and Wait
Tuesday, August 21, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz