Re: Pattern matching problem

[Chad Bourque]

Ben,

Just lookup data normalization. If you toss MySQL into your search criteria, you'll quickly find a MySQL resource on the subject. You didn't post the table structure. So, if your current table looks like this:

create table Users
(
  userId int not null auto_increment primary key,
  userName varchar(20) not null,
  firstName varchar(20) null,
  lastName varchar(20) null,
  stock varchar(250) null
);

It would become something more like this:

create table Users
(
  userId int not null auto_increment primary key,
  userName varchar(20) not null,
  firstName varchar(20) null,
  lastName varchar(20) null
);

create table Stocks
(
  symbol char(4) not null primary key,
  name varchar(20) null
);

create table UsersStocks
(
  userId int not null,
  symbol char(4) not null,
  constraint pk_UsersStocks
    primary key (userId, symbol),
  constraint fk_UsersStocks_Users
    foreign key (userId)
    references Users (userId)
    on delete cascade
    on update cascade,
  constraint fk_UsersStocks_Stocks
    foreign key (symbol)
    references Stocks (symbol)
    on delete cascade
    on update cascade
);

Now, each user can have as many stocks as they want and each stock can be watched by as many users as you have. If you don't have or don't need/want to keep a list of available stocks, you could just do something like this:

create table Users
(
  userId int not null auto_increment primary key,
  userName varchar(20) not null,
  firstName varchar(20) null,
  lastName varchar(20) null
);

create table Stocks
(
  userId int not null,
  symbol char(4) not null,
  name varchar(20) null,
  constraint pk_Stocks
    primary key (userId, symbol),
  constraint fk_Stocks_Users
    foreign key (userId)
    references Users (userId)
    on delete cascade
    on update cascade
);

HTH,
Chad