[Starblaster100]

If I'm understanding you correctly, you're having trouble between the Customers and Addresses Table?

I would add the foreign key Customer ID into the Addresses Table (rather than have the Address ID as a foreign key in the Customer table) and then have the Foreign Key AddressID in the Orders Table.

Order Table
OrderID (PK)
CustomerID (FK)
AddressID (FK)
etc.

Customers
As you have but no AddressID (FK)

Address
As you have +
CustomerID (FK)


If I've miunderstood the problem, please let me know, and we can try again

[Kyek]

Edit: I got starblasted D:

It sounds complicated, but it's actually very easy . Without spending more time reading your schema, I'm not sure which tables are many-to-many -- but imagine for a moment that products can be placed in more than one category. So Categories can have many Products, and Products can have many Categories. Many to many. Your junction table looks like this:

+----+------------+-------------+
| id | product_id | category_id |
+----+------------+-------------+
| 1  | 479        | 2           |
| 2  | 479        | 4           |
| 3  | 512        | 2           |
| .. | ...        | ...         |
+----+------------+-------------+

The 'id' field is optional -- you could also do this by making your primary key a dual-column key. That's all personal preference . Either way you do it, make sure you have INDIVIDUAL indexes on your product_id and category_id fields.

And that's it! You just put a row in there for every relationship. In the example above, product #479 belongs to category 2 and 4. Category 2 contains two different products. All you need to do to get a list of all the products in a certain category is JOIN this table to the products table, and select any rows that match the category ID you need. Do the opposite to find the categories that belong to a certain product

Good luck!

[deucalion0]

Hey Starblaster100, thanks for your reply. I think there has been a small misunderstanding.

I have five many to many relationships in my ERM diagram. according to Connelly and Begg I must have another table in between any tables that have a many to many relationship. Apparently you create a new table with a name and give it the primary key of both tables and also teh foreign key of each as well. So I must then have to do this five times increasing the size of my ERM from 7 tables to 12.

I was hoping fro clarification on this, and how to do it and why.

Does that make sense?

Thanks!

[Starblaster100]

I see, I think I jumped the gun a bit there! (I was thinking more along the lines of: Addresses should really be attached to an Order - if I have multiple addresses, how would you know which to post to?!)
See Kyek's post above, it explains how to use table mappings very nicely

[deucalion0]

Ah Kyek, I never saw your post. OK, I think I understand your logic.What I was thinking about was; if I have a new middle table what exactly is the relationship with the two table with the new one, is it a 1 to many relationship now? I was always getting confused, because I split products up into its own table, this will contain every single product, but these products can be dividing into categories, and these into a range. So a MENS BLUE PULLOVER is a product of category type MENS and the range is within the PULLOVER range, if this makes sense? It has been a long process doing all this trust me!

Thanks!

[Starblaster100]

Expanding on the example given above - Say we have 3 tables now:

The 2 existings ones:
Product
Category

And our new Mapping Table:
ProductCategoryMap

Product to ProductCategoryMap = 1 to many relationship.
Category to ProductCategoryMap = 1 to many relationship.



So initially, your ERM shows :
Product = *..* = Category

We now have
Product = 1..* = ProductCategoryMap = *..1 = Category


Essentially we have turned a many to many relationship into a 1 to many to 1. This intermediate table allows us to combine products and categories into as many different pairing as we want.


You'd do a similar thing for the Range table to the Cateogry table.

Category = 1..* = CategoryRangeMap = *..1 = Range.

This again will allow you to combine as many categories and ranges into as many pairings as you want.

[deucalion0]

starblaster that's an excellent breakdown, thanks. I just have one last query, do I use the primary key from product and category and merge them as a composite primary key in the new table? Also how does this then work with creating a foreign key? If I could just figure out the key part then I am done I think!

Thanks!

[Starblaster100]

2 parts to your question, lets break it down:

deucalion0, on 29 December 2011 - 02:42 PM, said:

starblaster that's an excellent breakdown, thanks. I just have one last query, do I use the primary key from product and category and merge them as a composite primary key in the new table? Also how does this then work with creating a foreign key? If I could just figure out the key part then I am done I think!

Thanks!

Regardless of whether we choose to create an identity column (I'm in favour of doing so), we still need to at least create a primary key, so ...

We will probably want to create a composite primary key on these mapping tables. Why? It will give us a single column with a unique constraint on the 2 columns we are referencing, and will be indexed. Regardless, there are 2 approaches.

Take Kyek's example:

+----+------------+-------------+
| id | product_id | category_id |
+----+------------+-------------+
| 1  | 1          | 2           |
| 2  | 1          | 4           |
| 3  | 2          | 2           |
| .. | ...        | ...         |
+----+------------+-------------+

Approach 1:

Problem 1: We need a primary key
Solution 1: Our identity column, "id", acts as the primary key.

Problem 2: Product 1 is in Category 2 and 4. Once we know this, there is no need to ever insert Product 1 into Category 2 or 4 again. We want to stop inserting a certain product into a category it is already defined in it.
Solution 2: Create a unique constraint on these 2 columns.

OR Approach 2:

Solution 1 and 2: Create a Primary Composite Key on product_id and category_id. This will give us that unique constraint, as well as act as the identity column and primary key for the table. (In some databases, composite prmary keys = unique constraint)

The difference?

Approach 1 has a separate identity column (primary key, and indexed), with a unique constraint on the other 2 columns.

Approach 2 has no identity column, with the unique constraint acting on both columns (indexed, both primary composite)

I hope I explained that clearly?!

deucalion0, on 29 December 2011 - 02:42 PM, said:

starblaster that's an excellent breakdown, thanks. I just have one last query, do I use the primary key from product and category and merge them as a composite primary key in the new table? Also how does this then work with creating a foreign key? If I could just figure out the key part then I am done I think!

Thanks!

Create foreign keys as usual, referencing the correct columns in the other tables. This doesn't change whether we are using composite primary keys or not.


Hope that helps!


Edited for clarity and spelling

This post has been edited by Starblaster100: 29 December 2011 - 07:36 PM

[Daniel15]

The table in between the two tables involved in a many-to-many relationship is a weak entity called a junction table. To name this table, I normally combine the two table names (eg. CategoryProduct, CustomerAddress)

Also be careful of singular vs plural - Your ERD has some singular (eg. Category and Range) and some plural (eg. Products and Customers). This will often confuse you if you create the database and start writing queries for it

Quote

do I use the primary key from product and category and merge them as a composite primary key in the new table?

Yep - This is what I'd do

[Scipio]

Maybe you should use a database modelling software like mysql workbench, it's quite handy at solving issues like this.

[Daniel15]

You need to understand how things are done before using modelling software

[deucalion0]

Thanks a lot guys, many tips there, a great help to me! I always get confused when my learning materials are disorganised! I am gonna have to rethink my whole ERM now I think, I am even thinking that Products category and range are not a many to many relationship now. Ah well the joys of learning!

Thanks again guys for all your input, its appreciated.

[Brewed]

This has been quite helpful. A stab in the dark but I'm going to guess this is for www.viga.co.uk... And looking at the link location you're currently a student at Abertay and this might even be your coursework for Database and Internet Application Design.

I'm doing the same thing, I googled Connolly & Begg 9 step process and found this

[deucalion0]

Brewed, on 03 January 2012 - 12:58 PM, said:

This has been quite helpful. A stab in the dark but I'm going to guess this is for www.viga.co.uk... And looking at the link location you're currently a student at Abertay and this might even be your coursework for Database and Internet Application Design.

I'm doing the same thing, I googled Connolly & Begg 9 step process and found this

Haha yes you are right on all accounts, I am at Abertay. This forum is the best you will find for help with anything as long as you try and figure things out yourself first.

This coursework is gonna be huge....