More efficient (foreign keys)...?

MarkB423

Which is more efficient:

Schema #1:

TABLE1: productID (auto-inc) | Name | Description | Price | etc...

TABLE2: productID | SerialNo | Manufacturer | etc...

Schema #2

TABLE2: infoID (auto-inc) | SerialNo | Manufacturer | etc...

Also, in Schema #1, TABLE2, what attributes should the productID field have besides "UNIQUE" to prevent the database from searching one record at a time? (or Schema #2, TABLE1, infoID field)

nemonoman

schema 2 is more efficient.

if using MySQL autoincrement columns will already be unique, indexed and NOT NULL, all good attributes for speedy searches.

Especially for joins between these tables.

I always create an indexed unique (usually autoincremented) id for tables that will be foreign keys.

ahundiak

Depends on what you mean by 'efficient'. For schema 1 create an index with something like

create index table2_product_id table2(productID);

With the index, a join between the two tables will be as fast as in schema 2 and you have one less field (infoID) to worry about.

nemonoman

Take a look at schema 1. It requires a unique compound key, not simply an index on product id. Product to Info is a 1 to many relation.

So info record must be unique by combining attributes.

My impression is that the unique attributes of an info record are:

(product_id Manufacturer serial no)

As nothing guarantees that 2 manufacturer's serialnumbers won't overlap.

If you want to be less rigorous, you can make a 2 column compound key

(product_id serial no)

and hope for the best.

serial no will of necessity be a varchar type column, since nothing guarantees that a manufacturer use a number, or a string of a fixed length. Varchar indexes typicallly are larger than integer indexes.

I may be mistaken, but I believe when creating a compound key index, MySQL generates indexes for both columns and a third compound index.

So that compound key index will take some more overhead than a simple autoincrementing not null integer id.

You are correct in noting, however, that the addition of this index key is not required by the schema if the columns are properly managed.

In fact the downside of my approach is the potential for inserting a row that is in all respects a duplicate, except for the info_id attribute.

MarkB423

Originally posted by nemonoman
schema 2 is more efficient.

if using MySQL autoincrement columns will already be unique, indexed and NOT NULL, all good attributes for speedy searches.

Especially for joins between these tables.

I always create an indexed unique (usually autoincremented) id for tables that will be foreign keys.

Let's assume that "Manufacturer" is actually something unique to that product and that product only (such as shipping information, or pricing, etc.)

Now, wouldn't Schemas 1 and 2 perform almost the same, as said by ahundiak, if I make the product_id field of TABLE2 in the first Schema Unique, NOT NULL, and indexed?

nemonoman

Perhaps I'm overthinking this, as product database design has been a big part of my experience.

I would have thought that manufacturer could simply be a column in your product table.

One product -- one manufacturer?

That's a column.

One product -- many manufacturers? (It's not uncommon for versions of a single product to be produced by many manufacturers.)

That's a related table.

One product -- one serial number? Or many? Typically serial numbers refer to specific instances. Each of your Apple III has a different serial number. That's a one-many relationship, demanding a related table with a foreign key.

If only one attribute per entity exists (like one mfg / product) -- collapse the info into columns in the entity's table.

If multiple attributes (potentially) exist for an entity (create a related table).

If you have a one=one relationship between prod & info, then ahundiak's approach works great, you don't need a separate id.

In fact you don't even need another table at all.

nemonoman

I have just reread your initial post and now believe I missed the point.

I based my thinking on my experience, not on your question. This caused my answers to be wrong.

The answer to your QUESTION as you asked it is: just use one table...as you describe them, you don't need two.

My experience tells me that your schemas require some re-thinking, however. You'll need some one-to-many relationships in there, or I miss my guess.

At that point it would be worthwhile to revisit the efficiency questions that spawned this thread.

My apologies to Mark and ahundiak.

ahundiak

Actually, schema 1 does allow you to have a 1 to many relation between table 1 and table 2 i.e. a given product could have multiple entries in table 2. And all you need is to have an index on the productID column in table 2.

To restate, a table does not have to have an unique key just to participate in a 1 to many relationship.

I agree that the schemas will probably need rethinking. There is will probably need to be a many to many relation between table 1 and table 2 in which case a 3rd table is required.

nemonoman

Ahund...Looking at original post's schema 2:

TABLE2: infoID (auto-inc) | SerialNo | Manufacturer | etc...

Where infoID is included as a column in Table1 (one product, one infoID) ...

This where I start getting confused.

You also wrote:

Actually, schema 1 does allow you to have a 1 to many relation between table 1 and table 2 i.e. a given product could have multiple entries in table 2. And all you need is to have an index on the productID column in table 2.

Yes, rethinking this I agree with you. I think I am assuming too much. I sort of automatically assume that we're at the 3rd normal form.

I'm new to the PHPbuilder bulletin board, and there is a wide variety of experience and intelligence displayed here. In the future I will consider my answers more carefully.

You finally wrote:

There is will probably need to be a many to many relation between table 1 and table 2 in which case a 3rd table is required.

The many-to-many relationship might be a good topic for a bulletin board note (or an article, for that matter).

MarkB423

I think all of this confusion is my fault, I just made up a quick schema for determining which method of joining or relating two tables is best in a ONE to ONE relationship. I understand the information I had in Table1 and Table2 might not necessarily fit into that idea of a one to one relationship, but please pretend it does...

A more specific schema would be Table1 having product information and Table2 having the shipping data for that product... though it was stated that I could put all of this in one table, I would like to have it in two tables. For instance, if the shipping data is being updated and the rest of the information is not, there is no point in locking up the product information table while performing an update.

Here's what it boils down to:

I am just curious if I can get the same speed on a SELECT statement if, in Schema #1, I reference infoID in TABLE2 by an auto-incremented Primary Key or by a Unique, Not Null, Indexed Primary Key.

ahundiak

I have not seen any benchmarks but I would not think there would be any difference in performance.

mattr

Originally posted by nemonoman
I may be mistaken, but I believe when creating a compound key index, MySQL generates indexes for both columns and a third compound index.

Nope, just a single B+ tree.

Originally posted by nemonoman
So that compound key index will take some more overhead than a simple autoincrementing not null integer id.

To some degree, yes, but remember slapping an INT on each row costs 4 bytes per row, which would need to be accounted for.

Further if queries are going to be performed on the two predicates you are advocating not using as the primary key then they add more indexes.

Selection of a primary key as "slap an auto-increment on every table" is extraordinarily short-sighted and should not be a hard and fast rule. I am not suggesting an auto_increment (more formally called a surrogate key) is not appropriate here, just that caution must be used and that a blanket statement is not approprate in database design.