Database many-to-many intermediate tables: extra fields

I have created a 'shops' and a 'customers' table and an intermediate table customers_shops. Every shop has a site_url web address, except that some customers use an alternative url to access the shop's site (this url is unique to a particular customer).

In the intermediate table below, I have added an additional field, shop_site_url. My understanding is that this is in 2nd normalised form, as the shop_site_url field is unique to a particular customer and shop (therefore won't be duplicated for different customers/shops). Also, since it depends on customer and shop, I think this is in 3rd normalised form. I'm just not used to using the 'm开发者_StackOverflow社区apping' table (customers_shops) to contain additional fields - does the design below make sense, or should I reserve the intermediate tables purely as a to convert many-to-many relationships to one-to-one?

######
customers
######

id INT(11) NOT NULL PRIMARY KEY
name VARCHAR(80) NOT NULL

######
shops
######

id INT(11) NOT NULL PRIMARY KEY
site_url TEXT

######
customers_shops
######

id INT(11) NOT NULL PRIMARY KEY
customer_id INT(11) NOT NULL
shop_id INT(11) NOT NULL
shop_site_url TEXT //added for a specific url for customer

Thanks

What you are calling an "intermediate" table is not a special type of table. There is only one kind of table and the same design principles ought to be applicable to all.

Well, let's create the table, insert some sample data, and look at the results.

id cust_id  shop_id  shop_site_url
--
1  1000     2000     NULL
2  1000     2000     http://here-an-url.com
3  1000     2000     http://there-an-url.com
4  1000     2000     http://everywhere-an-url-url.com
5  1001     2000     NULL
6  1001     2000     http://here-an-url.com
7  1001     2000     http://there-an-url.com
8  1001     2000     http://everywhere-an-url-url.com

Hmm. That doesn't look good. Let's ignore the alternative URL for a minute. To create a table that resolves a m:n relationship, you need a constraint on the columns that make up the m:n relationship.

create table customers_shops (
    customer_id integer not null references customers (customer_id),
    shop_id integer not null references shops (shop_id),
    primary key (customer_id, shop_id)
);

(I dropped the "id" column, because it tends to obscure what's going on. You can add it later, if you like.)

Insert some sample data . . . then

select customer_id as cust_id, shop_id
from customers_shops;

cust_id  shop_id
--
1000     2000
1001     2000
1000     2001
1001     2001

That's closer. You should have only one row for each combination of customer and shop in this kind of table. (This is useful data even without the url.) Now what do we do about the alternative URLs? That depends on a couple of things.

• Do customers access the sites through only one URL, or might they use more than one?

If the answer is "only one", then you can add a column to this table for the URL, and make that column unique. It's a candidate key for this table.

If the answer is "more than one--at the very least the site url and the alternative url", then you need to make more decisions about constraints, because altering this table to allow multiple urls for each combination of customer and shop cuts across the grain of this requirement:

the shop_site_url field is unique to a particular customer and shop (therefore won't be duplicated for different customers/shops)

Essentially, I'm asking you to decide what this table means--to define the table's predicate. For example, these two different predicates lead to different table structures.

• customer 'n' has visited the web site for shop 'm' using url 's'
• customer 'n' is allowed to visit the web site for shop 'm' using alternate url 's'

Your schema does indeed make sense, as shop_site_url is an attribute of the relationship itself. You might want to give it a more meaningful name in order to distinguish it from shops.site_url.

Where else would you put this information? It's not an attribute of a shop, and it's not an attribute of a customer. You could put this in a separate table, if you wanted to avoid having a NULLable column, but you'd end up having to have a reference to your intermediate table from this new table, which probably would look even weirder to you.

Relationships can have attributes, just like entities can have attributes.

Entity attributes go into columns in entity tables. Relationship attributes, at least for many-to-many relationships, go in relationship tables.

It sounds as though, in general, URL is determined by the combination of shop and customer. So I would put it in the shop-customer table. The fact that many shops have only one URL suggests that there is a fifth normal form that is more subtle than this. But I'm too lazy to work it out.