many to many or repeating rows

BEFOR

A basic question, whether this should be placed on a many to many table or a table with many rows. I want to list the counties that each merchant-user provides products in ('C" table):

A many to many matching userid's with lookup id's
A_userid A_Bprimaryid

B (lookup for "A")
B_primaryid B_name/label col

C many to many like this? (composite index)
C_userid C_counties

Or should this be on a repeating row table, with each county listed as a seperate row?

C repeating row (single primary)
C_primaryid C_userid C_county

If searching by States listing counties, or if searching by product AND county to find particular merchants throughout the country -- is this a many to many, or could the user-merchants be associated in a repeating row table?

Sxooter

using the join table in between is generally considered the better option, and the least likely to cause issues at a later date due to needs to change your schema.

create table users (userid int primary key, username text);
create table items (itemid int primary key, itemname text);
create table users_to_items (user_id int references users(userid), item_id int references items(itemid));

BEFOR

okay, by your example, what if the user-items needed to be known by counties within the united states. How would the counties tables play? So each could be found by counties and items/counties

Sxooter

Assuming there's no natural key to bind those two tihngs together, you'd need a user-ites to counties join table that simply containe references to the pks of those two tables.

BEFOR

For counties:

Not sure I understand.

For the [counties table:
Will there be a two col/composte index?

Will there be a table with its own primary (the only table index column) then C_userid C_county (both not indexed) Each county appearing on repeat rows.

Sxooter

There should be a table with nothing but counties, non-repeating. There should be a table of users, also non-repeating. Whatever is the primary key for each of those is used in a join table something like:

create table user_to_county (user_id int references users (id), county_id int references county (id));

Do you have something more complicated than that, and if it is, can you make it less complicated to fit this model, or do you need a more complex model to handle the many to many relationship?

BEFOR

Okay, this is a many to many.

Without getting too complicated, I wondering how searches will be make that account for (2) many to many tables then. Included in the join is a runtime table (current time, that) and a directory listing (both having DESC LIMIT 1 for the last row)

The many to many for: user_to_county along with the many to many users_to_items

-searches wil be by county and by items
(also by zip code radius, but that's another story- should have zip and county database within a day or two)

Knowing this will form the basic structure for the entire site.