How should I go about this?

[jscribe]

Hi Guys,
I'm kinda new to mysql and have a question.

Say you have a scenario were you have information that needs to be stored, but in groups.

So I might be running a business that requires me to keep track of hair appointments.

Each appointment is a job.
Every job could have multiple services.
every service could have multiple products associated with it.

I would like to make it so that the mysql table could store multiple instances of products in a service and multiple services in a job.

Can this be done? Any advice would be much appreciated!

[Stewart Ross]

Best advice I can give you is to read up on relational database design. We have an introductory article on Database normalisation and table structures in our Access section which is a very good place to start.

-Stewart

[jscribe]

Thank you stewart, i'll check it out!

[jscribe]

Stewart, thanks again for the guide, that was helpful. I still have a question if you dont mind:

I have separated my tables in such a manner that has a primary key relating an id per service and then a separate table with it's own id based on products available.

For simplicity:


Services
S_id Name
0 hair cut
1 color hair
2 trim eyebrows


Products
P_id Name
0 hair color brown
1 Toe nail clippers
2 shampoo


How would these tables then be combined into a single table to show a single invoice?
That tracks an invoice id(single), the services used (multiple), and Products used (multiple).

[Stewart Ross]

Tables themselves do not get combined at all; the whole purpose of relational design is to separate out related entities, not to combine them. Relationships between these tables are defined and set so that the one-to-many relations, in particular, are known and resolved. This is the thrust of the article I suggested you read.

There are occasions when you need to define a linking table to resolve many-to-many relationships (decomposed into two one-to-many relationships), but I don't think that is what you are asking here.

When you need to combine tables together to produce invoices etc this is done by devising a suitable query on the joined tables. The base query has the effect of providing you with a logical view of the data that does not reflect the way the data is stored. The table structure, if properly designed, is free from the potential for update and other anomalies that will always result from implementing un-normalised relationships.

A report can be based on an invoice query so that you can print a suitable invoice for the customer, for example.

I would urge you to explore relational design in depth before you move on - it takes much practice to get it right. Get yourself a good introductory book and work through the exercises - there is no substitute for this, as you will only learn how to normalise data by implementing real examples.

-Stewart

[jscribe]

Thank you stewart, that was very helpful!