Above a simple database schema: good and bad scores? with example
I apologize for the protracted, extravagance of this, but I'm at 3 o'clock, afraid that I will have to work tomorrow and deal with this database ... it just feels wrong, but maybe I'm wrong and just love things have come my way ... Please tell me what you think.
Our database schema looks like this:
page: id, label, contentid, parentpageid
content: id, xmldata
We have about 4000 records in the page table ...
I've included a greatly simplified page hierarchy that looks like a code list at the bottom of this post.
The source of the problem is the fact that the database is fraught with these things:
- For each individual page labeled 'SET (there are 3) there are 16 children, each labeled with SECTION numbers.
- Each individual SECTION has a child named "O" and a child named "Information"
- Each "About Me" page has a set of kids labeled: "About Things", "About Materials", and "About Other Things"
Note that although children are tagged the same, they are likely to have different (but similar) content.
I have some serious problems with this circuit given the data and would like to know if I am justified and if there is a good solution or something I should read.
The main problem is this: There are -lot- duplicates and duplicate hierarchies.
edit: Another problem: if there are children on the page then the contentid is ignored. Empty space?
I am also having problems with having a hard time figuring out where I am in the tree ... what type of object am I looking at?
For example, TASK nodes should display differently than other nodes ... and the only way to find out if a node is a task, if it's the parent label "Tasks" (or "Tasks") or "ToDo"). I tried to use the "depth" node, but that doesn't work because sometimes other elements require a different display at the same depth.
The suggested solution was to add a "Type" field ... and then the UI decides how to display the node (for example, the "Nav" nodes go into the main tab bar (like "O") and type "Task" dropdown nodes in the side panel), but again, that's not the case either.
In addition, there is currently a NO method for sorting items. So far we have been lucky that the data entered the database in the correct order, they want to display it, and so far there have been no exceptions.
They want to add a sort order field to fix this. it seems like a lot of manual customization work, especially if they ever want to reorder a specific set of menus ... (for example, insert a new child in each O node), although I'm guessing a simple script can write, do it ... although my argument is that I should be able to change it in one place and then it just did.
I've never seen a database like this, but they claim that all databases are set up. This is what they are for. Am I going crazy or is this a ludicrous way of setting up any database with this multiply duplicated hierarchy (also remember that the below hierarchy has far fewer duplicate records and duplicate trees than the actual page hierarchy).
Honestly, I would like to suggest a better solution, but I'm not really sure what it is because there are 8 different types of "O" nodes in the real hierarchy, some of which contain more nodes with more children ... but childish shortcuts and orders are always the same. Do I need to create a table for each of these page types?
I noticed that this is also a common problem for websites where they can have many pages, all with the same set of children ... but in no way say: ok I want this page to inherit this set of children. but I want them all to have different content. now I want to rearrange the children in one place and update them. Is there a simple and easy way to solve this problem?
SET1
SECTION1
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Tasks
-A
-B
-C
...
-H
-Data
SECTION2
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Tasks
-A
-B
-C
...
-H
-Data
...
SECTION16
SET2
SECTION1
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Jobs
-1
-2
-3
...
-8
-ToDo
-A
-B
-C
...
-H
-Other Data
SECTION2
-About
-About Things
-About Stuff
-About Other Stuff
-Info
-Jobs
-1
-2
-3
...
-8
-ToDo
-A
-B
-C
...
-H
-Other Data
SET3 (Exact same setup as SET2)
...
THANK!
source to share
A few thoughts on some of the things you have stated:
"The suggested solution was to add a Type field ..."
If there is an obvious need for some of your business stuff to be "printed" because there is some relevant difference somewhere between objects of such different "types", then from a relational database, which is a key indication that what you want / need to set up multiple tables (one for each type), at least at the logical level.
How you organize it PHYSICALLY is a different matter and it is a sad but true reality that SQL cannot make a proper distinction between logical / physical, and it is an even sadder and truer reality that due to SQL failure at this level, many the developers have no clue at this level.
"Also, there is currently no way to sort items."
Be aware that at the logical level, a relational database does not know the concept of ordering. Ordering is a presentation problem. The dbms / only database is only concerned with the fact that its physical design characteristics (indexes in particular) can be used to provide the requested items that the client / user requests. Whether or not such indexes are included in the project, this is a physical database design solution.
"They want to add a sort order field to fix that."
Sounds somewhere between "pretty silly" and "completely insane" to me (depending on what that sort order field should contain).
"but they claim that this is how all databases are configured for what they are intended for."
Sounds like a fundamental ignorance to me. But I'm only reading your part of the story, which is possibly biased.
"the hierarchy below has far fewer duplicate entries and duplicate trees than the actual page hierarchy."
I'll tell you what databases are for: they are for registering assertions / statements of fact. Each row in the database represents an actual statement that is considered true. And now for duplicates, "If something is true, then repeating it twice won't make it any more correct." Copyright EFCodd. No database should contain duplicates (which, of course, should mean the same thing).
source to share
I think Erwin's analysis indicates that there is a lot of confusion among the members of your group about how a relational database should be used. From your schema, it looks like you are basically re-creating the filesystem in your database. This is pretty silly, your web server already has one. I humbly suggest that you revisit your design or consult with a website design consultant before going any further with your system. If you continue to be guided by what you are doing now, I think the project might be in trouble and could be wasting a lot of money / time.
I think you have a better understanding of the data and how to model it, but you might have to go through a couple of iterations until you get basically a nail to fit your requirements and what you need from the -driven database site system.
Good luck with your database!
source to share
I think you are beating yourself to death. If the ER diagram of the database doesn't exist, draw it - it looks like you have a reasonable understanding of the content and normalization.
By using this ERD, you should be able to solve most of the problems mentioned above quite easily.
source to share
secoif is the reason they call it a relational database. Without multiple tables and using joins, what is the point of using a database, a flat file can also be used. Your so-called "other employees" can point and laugh whatever they want, but the true power and effectiveness of a database driven application is when you start using joins and normalize your data.
source to share
You can create your tables like this:
MenuItem: ID,
MenuItemContent Name : Id, Content
MenuItemHierarchy: ParentMenuItemId, MenuItemId, ContentId, SortKey
Usually, however, I:
MenuItem: Id, Name, ContentId
MenuItemContent: Id, Content
MenuItemHierarchy: ParentMenuItemId, MenuItemId, SortKey
And don't use menu items to display other content.
source to share