Carl Schrader wrote:
> Field Special 1 has a related field with a Start Date and another field
> for End date.
> Field Special 2 has a related field with a Start Date and another field
> for End date.
> Field Special 3 has a related field with a Start Date and another field
> for End date.
>
IMNSHO, Any time you have a design like this where you have somedata_1,
somedata_2, etc., it much better to change it so that the columns appear in
a long table and add an extra column e.g. special_num. In your case:
special_num tinyint UNSIGNED,
start_date date,
end_date date,
special char(255),
etc..
1) This makes it easy to add more specials without doing an alter table.
2) You can get the same output as your original design by doing self joins:
SELECT T1.special AS special_1, T2.special AS special_2, etc. FROM table T1,
table T2 WHERE T1.special_num=1 AND T2.special_num=2 AND etc..
> I will probably need to add up to 3 other fields to keep track of the
> last shown date.
Add a field to the above table.
> one. What complicates this is that there are UP TO to 3 specials per
> record. Some of them may not include any specials at all. Some may have
> 1 or 2. Special 1 should be shown before Special 2. etc. Only 1 Special
[snip]
If you have a master table with specials, you can do a left join to get
records where there are no specials. You can set up some sort of linkage
between the main table an the specials, eg. an auto_increment field in the
main table or perhaps a part number, etc..
--Bill Adams
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
