Tables Design for three related entities

The scenario is as below:

       n   ownership    1
stocks <-----------开发者_如何学JAVA--------- users

      n    belong to         n
users -----------------------> sectors 

       n    having            1
stocks <---------------------- sectors

My design for them is used 6 table: T_Stocks, T_Users, T_Sectors, T_UserStocks, T_UserSectors, T_SectorStocks, but I do not think is a good design. If you have any idea about it, please help share it with me.

You are right, that is not a good design. (Sorry! :P) Since each stock has only one user as its owner, you can just put an ownerId column in your stocks table. Give it a foreign key constraint, and remember to put an index on it!! Similarly, each sector has only one user, so you can have a userId column in sectors as well. (But this seems backwards to me; did you intend to have this relationship the other way around??)

However, at this point putting a sectorId in stocks breaks normalization, because you could make a stock whose user is not the same as the user of its sector. If you don't care about normalization, you can just pray you never get it screwed up. Alternately, since the combination of the second two relationships imply the first, you could just jettison the userId column on stocks, and have only a sectorId.

If users can only own stocks that are in sectors in which they participate, then maybe the sectors to users intersection (n:m) is not really necessary. Can a user be in a sector but own no stock in that sector? If a user is in a sector strictly by virtue of owning a stock in that sector, then your model is quite a bit simpler. Something like this:

SECTOR
  sector_id
, name_of_sector
, ... (anything else about sectors)

STOCK
  stock_id
, name_of_stock
, ... (whatever that pertains to the stock as a whole)
, sector_id     -- What sector is this stock in?

STOCK_CERTIFICATE
  stock_certificate_id
, stock_id        -- Which stock is it?
, quantity        -- number of shares, etc.
, user_id  NULL   -- who owns this stock, could be nobody?

USER
  user_id
, user_name
, ... (anything else about the user)

Now if you want to know who owns how much of what stock you just look in the STOCK_CERTIFICATE table. If you want to know what sectors a user is in, you would use a query like this:

SELECT DISTINCT
  A.sector_id
, A.name_of_sector
FROM SECTOR A 
  INNER JOIN STOCK B 
    ON A.sector_id = B.sector_id
  INNER JOIN STOCK_CERTIFICATE C
    ON B.stock_id = C.stock_certificate_id
WHERE
  C.user_id = {whatever your user ID is}

You can also do a similar distinct select going the other direction if you want to know which users are in a given sector.

EDIT: Accounting for USERs have a ROLE in the SECTORs to which they belong...

If users participate in sectors by having a role in the sector (as per @OP's comment) then you need to have an intersection table between USER and SECTOR as follows...

USER_ROLE_IN_SECTOR
  user_id    (PK, FK)
, sector_id  (PK, FK)
, role_id    (PK, might be FK if you also have a ROLE table...)

This presumes that users can have one or more roles in any given sector. If a user can have at most one role in any given sector, you would have to include a unique constraint on user_id + sector_id. If only one user can have a particular role in any given sector, then you would need a unique constraint on sector_id + role_id.

This new table handles the fact that users can participate in sectors without actually owning any stocks in a given sector. The other tables proposed originally, above are still the best way to record who owns what stocks and what stocks belong to which sectors. This model is in third normal form so there is no redundancy per se even though user_id and sector_id are repeated in the USER_ROLE_IN_SECTOR table.

You would need some other kind of constraint if you wanted to impose a business rule that says a user can't own stock in a sector unless they have some role in that sector. There is no clean way to do that type of constraint with plain vanilla referential integrity. It's the kind of thing that needs a procedural style check instead of a referential constraint.