#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Arlington, TX
    Posts
    171
    Rep Power
    12

    n:m table relations


    I'm new to database modeling, and currently using a visual tool called DBDesigner 4 to handle database creation.

    Let me just explain what I have, what I'm trying to achieve, then explain what I'm doing and ask if I'm on the right track or not.

    Here's what I'm doing:
    I want to make a table, InventoryTable - it gets it's unique, primary key from several seperate tables: products (which contains the prodID, prod_name, prod_styleNum, prod_costprice and a FK (catID) to a categories table), colors (colorID), sizes (sizeID) and has a fields of qty and sellprice in the actual table.

    I can do that, no problem, and the color and sizes for the products are seperate tables in n:m non-identifying relationships (which I'm not quite sure what that means, but it's basically a junction table that allows a product to have many colors from the colors table, and allows those colors to be selectable by many products (right?))

    Now, I'm not sure if I can use this same type of relationship for what I want to do next:

    I'm creating a saleTransactions table and an Invoice (basically sale order) table where the Invoice has several saleTransactions, where a saleTransaction is 1 row of the invoice (like, x products of y color and z size for i rate yields i*x amount), and the entire set of m saleTransactions yields the invoice_total.

    Basically an invoice --
    [ Sale No. <uniqueSaleId> on: <timestamp> to <custID> ]
    [ X product Color: y Size: Z Rate: I Amount: <i*x> ]
    [ A product Color: B Size: C Rate: J Amount: <j*a> ]
    [ Invoice Total: < <i*x> + <j*a> > ]

    [ X product Color: y Size: Z Rate: I Amount: <i*x> ] was a row from the SaleTransaction generated by the Inventory table (that's done application-side)

    Now, I don't know if n:m (non-identifying) is specifically terminology from DBDesigner 4, but I was hoping you guys could answer as to what that does, exactly, and if I'm using it correctly on my colors and sizes tables to let products have more than one color and one size per product from a list in a table(s) already predefined (colors, sizes).

    I want to allow the products and colors to have those seperate multiple selections but not be marked in the actual products or color or sizes tables themselves, but that 'junction' table in between, which I have marked as a 'weak entity: [checked] is n:m table', to record that product with prodID, say 7, has colors with colorID 2, colorID 4, colorID 6, and sizes with sizeID 1, sizeID 3, and sizeID 5.

    Am I doing this correctly with n:m relationships and that inner junction table?

    And then, if that's right, can I then have an Inventory table completely dependent on those values? Like, if a product has been entered into a form (which I'll make w/ PHP) and has sizes and colors marked from options populated from my colors and sizes tables, then that information is placed into the junction table _and_ an Inventory table row is created with that primary key generated from product, color, size and the form passes in the values for qty (in each color, each size seperate form fields the 'admin' has to place qty) to accomodate multiple rows? Like, an admin places "Shirt" with "Sizes XL, L, S" in Colors "Brown, Red, Green" and places in seperate qty boxes 6 of Size XL in color Brown, 3 of size L in brown, 0 of size S in brown, 3 of size XL in Red, etc., I want to create seperate rows in the inventory for each combination. Primary key'd be like: xlbrnshirt qty 6 (one row), lbrnshirt qty 3 (one row), etc.

    I'm pretty sure most of that will be handle application-side, but should I be defining the n:m (non-identifying?) relationship for this, or some other type?

    If you can explain part or all of these things, it'd help me out tons, thanks.
    -Ahad L. Amdani
    Programmer/Developer
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    first of all, when you translate a logical model to a physical model, all n:m relationships have to be converted to a pair of 1:m relationships

    so let's just talk about 1:m parent-child relationships

    a "non-identifying" relationship is one where the foreign key in the child is carried as data

    an "identifying" relationship is one where the foreign key in the child is carried in the primary key of the child

    in "junction" tables, which connect two tables (the ones with the n:m logical relationship), there are two foreign keys, one from each parent

    put them both into the primary key of the junction table, i.e. make them both identifying relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Arlington, TX
    Posts
    171
    Rep Power
    12
    So, you mean I should have a 1:m identifying relationship between products(1) and productColors(m) (the junction table) and between colors(1) and productColors(m) ?

    And when you were explaining non-identifying relationships and identifying relationships

    Originally Posted by r937
    a "non-identifying" relationship is one where the foreign key in the child is carried as data in the child?

    an "identifying" relationship is one where the foreign key in the child is carried in the primary key of the child
    (Non-identifying) So in the child, the FK from the Parent is carried as data in the child? And in identifying, same thing, but carried as part of the primary key?

    Sorry for the delay in responding, my internet was off at home due to some technical issues with the cable company.

    Btw: visited your website which led me to that KISS article - very intriguing.
    -Ahad L. Amdani
    Programmer/Developer
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Ahad
    (Non-identifying) So in the child, the FK from the Parent is carried as data in the child? And in identifying, same thing, but carried as part of the primary key?
    yeah, that's the idea

    and just to mess you up, if you follow along with me here, your junction table is possibly a ternary, rather than binary, relationship table, i.e. keys productid, colorid, sizeid

    this is so that you record exactly which sizes are available in which colours

    otherwise, you'd have a m:n binary relationship between products and colours, and a separate binary relationship between products and sizes

    which is okay if you plan to automatically offer every size in every colour....
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Arlington, TX
    Posts
    171
    Rep Power
    12
    Originally Posted by r937
    and just to mess you up, if you follow along with me here, your junction table is possibly a ternary, rather than binary, relationship table, i.e. keys productid, colorid, sizeid

    this is so that you record exactly which sizes are available in which colours

    otherwise, you'd have a m:n binary relationship between products and colours, and a separate binary relationship between products and sizes
    Ah, excellent idea for a ternary relationship, thank you! This will reduce that relationship count by a few

    And in regards to this:
    Originally Posted by Ahad
    I'm creating a saleTransactions table and an Invoice (basically sale order) table where the Invoice has several saleTransactions, where a saleTransaction is 1 row of the invoice (like, x products of y color and z size for i rate yields i*x amount), and the entire set of m saleTransactions yields the invoice_total.

    Basically an invoice --
    [ Sale No. <uniqueSaleId> on: <timestamp> to <custID> ]
    [ X product Color: y Size: Z Rate: I Amount: <i*x> ]
    [ A product Color: B Size: C Rate: J Amount: <j*a> ]
    [ Invoice Total: < <i*x> + <j*a> > ]

    [ X product Color: y Size: Z Rate: I Amount: <i*x> ] was a row from the SaleTransaction generated by the Inventory table (that's done application-side)
    For this part, I think I need a few tables: Invoices, to hold the transaction in its entirety for a customer, saleTransaction, to hold a row (an exact row of the inventory table but with qty ordered as opposed to in stock, and with details that are specified in the order, on the application side), and Inventory, which I have already.

    Say in my inventory table i have prodID 17, which in the products table says costprice is 1 buck and accordging to the inventory table with 10 in stock of their particular color & size, and margin is 50 percent, so in the sale itself by the customer via web form the price is at 1.50 and the customer wants 3 -- now this sale transaction should mark the exact information as the regular inventory but for qty be 3 and price be 1.50 - and then this 'transaction' is one part of a larger order, which is all recorded on the invoice, via FKs (many saleTransactions to one Invoice).

    For this scenario, should I then have all these tables, or is at least the "saleTransaction" table something that only has to be a "temporary" table?
    I still need to have the information on hand though - what sales were made, which invoices they belonged to, etc. (for reports and information gathering purposes) so it leads me to believe I'll need this saleTransaction table as well as the invoices table which has to mark multiple saleTransactions in that particular invoice.
    I realize my saleTransaction table can have duplicate data - 2 people order the same size xl black shirt, etc. - that isn't an issue.

    I hope I'm suceeding in trying to convey my approach to the particular problem. Here's a breakdown of my schema for this particular part of the system:

    Products is the table that holds item as information that will always be in the database regardless of if it's in stock or not, which is information held in the inventory table. Products are just things we have or do or will carry. They are entered, and sometimes edited, but never deleted (without the okay of the boss himself). The products table is also for ease of application development - the administrative end will see a form and they will put in a new product, pick from a list of available sizes, pick from a list of available colors, and then put in a description. They will then no longer have to worry about if the product is in the db or not. Everything at this point is handled via my php script, so a new inventory table row is created, defining seperate rows for each of the unique colors and sizes.

    The Inventory table controls what is in stock and is unique (medbrnshirt, lrgbrnshirt, xxlbrnshirt, etc.) and will be heavily used as orders will be placed for the products and shipments of products arrive, etc. This information is, again, entered by my script once the administrative end pushes the form button creating unique rows for unique items (and your ternary relationship idea will assist very nicely in this endeavor).

    Sales will occur, so invoices must be created, and those invoices must detail each unique item, pulled from the inventory table.

    Hmm, sorry for detailing all of this, but another question comes to mind, then, to the SQL experts and design analysts is: is this approach with multiple tables that retain similar (though not the same, ie inventory vs. products) information normalized and logical? I went through the three forms of normalization and (aside from the ternary relationship, which I might be able to use for another instance regarding the customer tracking system) I did not see information that was duplicated anywhere in the db where it was not supposed to be, and I did not find any fields not dependent upon the primary key of the table - excepting certain instances where all I wanted was like one e-mail address which must be entered for the customer, etc.

    And then, is this approach correct? Even if it were logical, it seems a bit off -- but this is my first serious project, and this is also my first complete database design and ecommerce system. I might just be second-guessing myself - these things make alot more sense in my head right now

    After all this checking for normalization and design perspective, I have yet a hundred more questions about FK contraints and enforcing data integrity, but I'll leave those for seperate threads Just to give you a reference point of view, I've learned php and practiced with it for about a year, on and off, taken 2 years of C++ in HS, hitting C in an intermediate programming course in college, and dealt with databases before only minorly. I'm reading "The Absolute Beginner's Guide to Databases" by John Petersen and I'm past design aspects, etc, wandering into the territories about the questions I'll ask in seperate threads :/

    I hope you guys can help me out, I'm trying to learn this not only for this project, which I'm volunteering for as opposed to being paid for (show my commitment to self-education? ), but because creating these types of systems fascinates me and helps me learn more about programming and design principles.

    Thanks a million for all your responses already, it's really cleared up a very foggy point in regards to the n:m relationships, identifying and non-identifying, etc. I did google this information, and checked several threads, but alot of it just doesn't click sometimes unless I'm told what it does with my particular situation.
    -Ahad L. Amdani
    Programmer/Developer

IMN logo majestic logo threadwatch logo seochat tools logo