By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,650 Members | 753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,650 IT Pros & Developers. It's quick & easy.

How should I go about this?

P: 4
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!
Dec 29 '11 #1

✓ answered by 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

Share this Question
Share on Google+
5 Replies


Expert Mod 2.5K+
P: 2,545
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
Dec 29 '11 #2

P: 4
Thank you stewart, i'll check it out!
Dec 29 '11 #3

P: 4
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).
Dec 29 '11 #4

Expert Mod 2.5K+
P: 2,545
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
Dec 29 '11 #5

P: 4
Thank you stewart, that was very helpful!
Dec 29 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.