Need advise on a little project.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need advise on a little project.

Post by Eran »

I never really got why but I just did as I was told. Many to Many relationships is taught at university as wrong, having one in an assignment project would cause you to be marked down, as your database would not be fully normalised. So you'd get rid of duplicates and columns not dependant on your primary key etc etc (i hate normalisation, and promptly forgot it after my exams)
Normalization is a must for any halfway decent database modeling, you shouldn't throw it away that easily. Many-to-many is a relationship you can't ignore, nor does it break normalization rules by itself.
The pivot/junction table is the additional entity which makes the tables theoretically not many-many.
Database schema is one form for representing data, it doesn't necessarily map directly to the entities in the system (though it's usually close enough). Not every table represents an entity in the system. Using a pivot table is the normalized approach to implementing a many-to-many relationship.

In your posts you implied that this relationship is "frowned upon" / undesired, you should be more clear in what you mean as to not mislead people who are not familiar with those terms.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Need advise on a little project.

Post by aravona »

pytrin wrote: Normalization is a must for any halfway decent database modeling, you shouldn't throw it away that easily. Many-to-many is a relationship you can't ignore, nor does it break normalization rules by itself.

Should inform my univeristy they are teaching wrong then lol. Normalisation as I was taught to was remove duplications and to not have any many to many relationships.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need advise on a little project.

Post by Eran »

Normalization is intended for improving the integrity and consistency of data in a relational database. You are welcome to read http://en.wikipedia.org/wiki/Database_normalization and review your class materials. Removing duplicates is a part of that, however ignoring / removing relationships that model the data correctly is quite simply a mistake.
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Need advise on a little project.

Post by Rippie »

Thank you everyone for replying to my post, now i just hope someone dont mind explaining to me, the best way of setting up my MYSQL tables.

So all in all, what i have been asked to make is a website that will list vendors and their products and then whether or not we have that resource available. The resource would be whether or not there is a person from technical department in the office and whether or not him/her is trained in that product. My manager has asked for a 14 days view to be shown.

If we could up with something so my php script knows the days and I only have to fill in whether or not there is a resource available.

Thank you again for any help in advance.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need advise on a little project.

Post by Eran »

Do you want to show 14 days for each vendor/product? ie, a user selects a vendor and then one of his products and then is shown a table of 14 days from now with technical people availability?
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Need advise on a little project.

Post by Rippie »

pytrin wrote:Do you want to show 14 days for each vendor/product? ie, a user selects a vendor and then one of his products and then is shown a table of 14 days from now with technical people availability?

Yes. that is what we are looking for.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Need advise on a little project.

Post by social_experiment »

Will the expertise of each technical person be aimed at only one product or will they have knowledge of multiple products?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Need advise on a little project.

Post by Rippie »

social_experiment wrote:Will the expertise of each technical person be aimed at only one product or will they have knowledge of multiple products?
It would be multiple products for each technical person.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need advise on a little project.

Post by Eran »

Regarding database structure, I would suggest a table each for vendors, products and technical people (let's call them techsupport). An additional table would describe the availability of technical people.
- A vendor can have many products, thus vendors to products is a one-to-many relationship. If products can belong to more than one vendor, that would be a many-to-many relationship (see below).
- A product can be known by many technical people. Additionally, each technical person can be familiar with many products. This relationship is called many-to-many.
- A technical person can be available on many days, thus techsupport to availability is a one-to-many relationship.

To recap, the database should look approximately as follows:
PK - primary key, FK - foreign key
vendors
- id (PK)
- name
... (additional attributes)

products
- id (PK)
- vendor_id (FK)
- name
... (additional attributes)

techsupport
- id (PK)
- name
... (additional attributes)

products_to_techsupport
- product_id (PK)
- techsupport_id (PK)

techsupport_availability
- techsupport_id
- available (date)

Depending on your requirements, the techsupport_availability table might need to be more refined in order to indicate specific availability hours per date.
User avatar
timWebUK
Forum Contributor
Posts: 239
Joined: Thu Oct 29, 2009 6:48 am
Location: UK

Re: Need advise on a little project.

Post by timWebUK »

pytrin wrote: products_to_techsupport
- product_id (PK)
- techsupport_id (PK)
You mean foreign key?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Need advise on a little project.

Post by Eran »

No, I meant a composed primary key. PRIMARY KEY ( `product_id` , `techsupport_id` )
Technically those two should also each have an individual foreign key for each of their respective tables. I wanted to make it clear though it's the same PK for both
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Need advise on a little project.

Post by social_experiment »

I created a script that does what you want ( from what I could gather from the post ). I used similar database concepts to what the other contributors suggested but i only used 3 tables. Included in the .zip file is a text file that contains the structure of the tables.

Change the details within 'detail.php' to match your own.
Vendors.zip
Contains 5 php files, 1 stylesheet, 1 text file with information about the database structure.
(4.28 KiB) Downloaded 44 times
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Need advise on a little project.

Post by Rippie »

Thank you very much for the script, i will have a little look at it later and get back to you if there is something in there i dont understand, but so far it looks ok.
Rippie
Forum Commoner
Posts: 76
Joined: Sun Jan 10, 2010 11:32 am
Location: Nottingham

Re: Need advise on a little project.

Post by Rippie »

Seem to have a small problem with your script. Details has been added to details.php file. the tables has been created but if i try the page config.php all i get is a blank screen. any ideas ? When i no longer need the config script page, is it then the vendor list i need to display ?

Rippie
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Need advise on a little project.

Post by social_experiment »

The config.php is the main file. I did the scripting object-orientated. Access vendorlist.php.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply