I second this. The way you are storing multiple data items in a single
column is very bad database design and fails the test for even the first
normal form. You are going to get very poor performance and usability
out of this table design. I know you said you inherited the data so its
probably not your fault, but I believe you will save yourself
considerable headache by refactoring the table rather than working
around the poor design.
John McCaskey
On Thu, 2004-10-07 at 13:03 -0400, [EMAIL PROTECTED] wrote:
> I would strongly recommend refactoring as the string transformation you
> mention cannot be accomplished without external scripting assistance.
>
> change your Bookings table to be (customer varchar(200), event_Id int(11))
>
> This way, each booking becomes one row in the table. Five bookings = five
> rows. This change also allows you to add additional fields (like price,
> date, contact info, etc) on the Bookings table so that you can track that
> information about EACH booking. I have no idea how you are tracking any of
> that the way you have the data now.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Neal K" <[EMAIL PROTECTED]> wrote on 10/07/2004 12:33:40 PM:
>
> > I have a quick SQL question that I hope someone can answer,
> >
> > I have two tables
> > Events { e_id int(11), e_name varchar(200) };
> > Bookings { customer varchar(200), event_list varchar(200) };
> > Event_list is a string of comma separated values, eg "1,2,4" where
> the
> > number corresponds to Events.e_id
> >
> > Sample data: Events { {1,'one'}, {2,'two'}, {3,'three'} }
> > Booking { {'john','1,3'} , {'jane','2,3'} }
> >
> > I would like some query that will return 2 rows
> >
> > John, 'one, three'
> > Jane, 'two,three'
> >
> >
> > Is this possible ?
> >
> > I inherited the schema and the data, so I would prefer not having to
> > refactor anything, unless I have to.
> >
> > Thanks in Advance,
> > Neal
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
