Messaging System - logic help needed

[aligray]

Hi Guys,

This is very much a logic based question rather than code.. I cant grasp my head around what the database will need to contain for me to be able to create this little system (the explanation is a simplified version of how it will work)

a business registers and states their favourite colour from a given list. So all members have a 'colour'

a member signs up, and composes a message. This message is given a colour and some content. All businesses that has assigned themselves to that colour receive this message. So each business has their individual inbox and can reply or delete a message without it affecting other business users.

So its a private messaging system with a twist. Can anyone help with some guidance on what tables I might need.

but from here I'm stuck with the logic behind this type of system. Does anyone know of a useful tutorial or can maybe shed some light? (I'm building in php and sql)

[sneakyimp]

Suppose you have a database table named 'companies' with these fields
companies_id
companies_name
* companies_colour

You could find all the companies of a given colour by this query:

SELECT * FROM companies WHERE companies_colour='PURPLE';

That would return all the records in the companies table who set their colour to 'purple'.

Sounds to me like your system would require additional tables for members, messages, etc. The messages table might be something like
messages_id
messages_colour
messages_companies_id
messages_subject
* messages_contents

If someone composed a message with color purple, you could use the above query to return all the companies with colour purple and then create a record in the messages table for each company returned -- each being identical except for the messages_companies_id which is how you assign the message record to a particular company. This approach would have a lot of redundant information but it would be simple.

You could also try creating a messages_to_companies table that just has
messages_id
companies_id

where you connect a message to a particular company. this would eliminate that redundancy.