Hello DP members, I have a question regarding designing of database.... I am designing a database of following type.... Please help by telling, if any many to many relationship is present in database design If many to many relationship is present, then where and how I can resolve it. I can also ask this question in different way like this: can we make a primary key of a table as foreign key in multiple tables??? Thanks for your time Jimmy
Not in the current design. Of course. This would be normal in a relatively complex database with a single items having many relationships, and you already have that with teh tbl_status PK. If I can make a suggestion, I'd be tempted to add another table between the tbl_products and the tbl_product_category. Although I don't know what the particular items are, it's often the case that a product will, or could, or would be handy if it did, belong to more than 1 category. It would be a simple 2 column table, something like "lnk_product_category" and would contain the product_id from the tbl_products and the product_category_id from the tbl_product_category. Of course, if a product could never belong to more than category then this wouldn't be necessary. The other thing is the tbl_status. Assuming status is something as simple as off/on (or the like), then surely a boolean data type would be more appropriate in each table rather than the relationship you have set up. Assuming it is more complicated, then will it be the case that the status text will be the same choices for all three tables? I just find it a bit odd that a tbl_admin status would share any characteristics with a tbl_product_category. But, as I've said, I don't know what your database will contain so it might make perfect sense as it is.
yes you can relate each others relation...like to one to one, one to many and many to many relationships.
yes you can use many to many relationship in database. this can be achieved by using outer join in sqlserver
YES, Can we make relationship of one datbase table with more than one tables example: The "Persons" table: P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger The "Orders" table: O_Id OrderNo P_Id 1 77895 3 2 44678 3 3 22456 2 4 24562 1 NOTE: "P_Id" column in the "Persons" table is the PRIMARY KEY and "P_Id" column in the "Orders" table is a FOREIGN KEY
it depends on what application you made. is it small to small scale, medium to medium or large to large scale transaction.