Basic Question from a newbie
-
There is probably a really easy answer to this series of questions but I'm sort of a newbie in designing a database. I know what a relational database is. I'm just not that familiar with designing one. My problem: Let's say I want to design a GUI interface for a kiosk for a library or bookstore. There could be several implementations and it could even be thought of as HTML links but the GUI looks nicer. The reason for this comparison is because I want the categories and sub categories to be dynamic, based on information stored in tables and obtained through queries. I was thinking that I could have a table of main categories called "cats", and a table of sub categories called "sub cats", and of course a table for all of the books with a sub-category field. If a GUI is used, a blob for the image that represents the category or sub category can be assigned a field in the tables. If I query the cats table and display all of the menu selections for the main categories and then when a main menu selection is made, say for "biographies", I can query for the second level menu items by looking for records in the "sub cat" table with a "Parent" field of "Biographies" which I imagine this could be only two records like "Biographies" and "Auto Biographies" and then drill down to the books table with a query to either of those two sub categories if a menu selection is made. The problem I have understanding is a many-to-one relationship and how to implement it. I've discussed the first and 2nd level menus but if there is a third level menu and a book, item, or even a fourth level menu, can belong to more than one sub category, basically a sub cat or item with more than one parent, what do I do? Does this require multiple blank fields in one of the sub cat or book tables? Or, should this be implemented through using a primary key that encompasses many fields, or, even possibly should I have a field that declares what level menu the sub cat belongs in?
-
There is probably a really easy answer to this series of questions but I'm sort of a newbie in designing a database. I know what a relational database is. I'm just not that familiar with designing one. My problem: Let's say I want to design a GUI interface for a kiosk for a library or bookstore. There could be several implementations and it could even be thought of as HTML links but the GUI looks nicer. The reason for this comparison is because I want the categories and sub categories to be dynamic, based on information stored in tables and obtained through queries. I was thinking that I could have a table of main categories called "cats", and a table of sub categories called "sub cats", and of course a table for all of the books with a sub-category field. If a GUI is used, a blob for the image that represents the category or sub category can be assigned a field in the tables. If I query the cats table and display all of the menu selections for the main categories and then when a main menu selection is made, say for "biographies", I can query for the second level menu items by looking for records in the "sub cat" table with a "Parent" field of "Biographies" which I imagine this could be only two records like "Biographies" and "Auto Biographies" and then drill down to the books table with a query to either of those two sub categories if a menu selection is made. The problem I have understanding is a many-to-one relationship and how to implement it. I've discussed the first and 2nd level menus but if there is a third level menu and a book, item, or even a fourth level menu, can belong to more than one sub category, basically a sub cat or item with more than one parent, what do I do? Does this require multiple blank fields in one of the sub cat or book tables? Or, should this be implemented through using a primary key that encompasses many fields, or, even possibly should I have a field that declares what level menu the sub cat belongs in?
Put all categories in the same table and let the subcategories reference their parent categories like this:
CREATE TABLE Categories (
id NUMBER(8,0) NOT NULL,
parentid NUMBER(8,0) NULL,
name VARCHAR2(36) NOT NULL,
more stuff..
)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_pk PRIMARY KEY (id)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_id_fk FOREIGN KEY (parentid) REFERENCES Categories (id)Then select the top categories with:
select id,name from categories where parentid is nullAnd a subcategory with:select id,name from categories where parentid = <ID>The self referencing foreign key blocks accidental deletion of a category that has subcategories"When did ignorance become a point of view" - Dilbert
-
Put all categories in the same table and let the subcategories reference their parent categories like this:
CREATE TABLE Categories (
id NUMBER(8,0) NOT NULL,
parentid NUMBER(8,0) NULL,
name VARCHAR2(36) NOT NULL,
more stuff..
)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_pk PRIMARY KEY (id)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_id_fk FOREIGN KEY (parentid) REFERENCES Categories (id)Then select the top categories with:
select id,name from categories where parentid is nullAnd a subcategory with:select id,name from categories where parentid = <ID>The self referencing foreign key blocks accidental deletion of a category that has subcategories"When did ignorance become a point of view" - Dilbert
Yes, exactly. :thumbsup:
-
Put all categories in the same table and let the subcategories reference their parent categories like this:
CREATE TABLE Categories (
id NUMBER(8,0) NOT NULL,
parentid NUMBER(8,0) NULL,
name VARCHAR2(36) NOT NULL,
more stuff..
)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_pk PRIMARY KEY (id)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_id_fk FOREIGN KEY (parentid) REFERENCES Categories (id)Then select the top categories with:
select id,name from categories where parentid is nullAnd a subcategory with:select id,name from categories where parentid = <ID>The self referencing foreign key blocks accidental deletion of a category that has subcategories"When did ignorance become a point of view" - Dilbert
Wow, I'm impressed. I had no idea it could be accomplished in so few of steps. Of course, I'm still trying to decode some of the "ADD CONSTRAINT" and REFERENCES language but I get the basic gist of what you're saying. I also had no idea that you could do the self referencing and thought, at minimum, it would require two tables. Very cool. Thanks so much !!