Any database design experts here?

Question

I need some help in understanding some basics on database design. I gone thru many books and videos courses but still unable to design simple scenario. The more I do the more it gets confused. Hope I get some help. Appreciate any help. Thanks

Answer 1

Part of why I wasn't very concrete was I was headed to bed when I found this thread. I hoped I could give a few ideas to help you sort this.

Now, after a night's sleep and some scratching away at scrap paper (perhaps small scraps, so you can move them around in relation to one another?), I have a bit more potential help.

It looks to me like the 3 major subdivisions are Process, Region, and Line of Business. Process and LoB are both subdivided multiple times, and Region may or may not be.

If there's a fair amount of data for the subcatagories/subLoBs, they may each want their own table (with a link to their super-divisions).

For example, a table with columns like:

ID, subcategory name, category name, category ID, databit1, databit1, databit3, etc

The ID is for your primary key, and for reference in the category table. Likewise the category ID field here is a reference to which category this sub belongs to.

For the Categories:

ID (Becomes category ID in the child table), category name, subcategory name, subcategory ID, databit1, databit2, databit3, etc.

Note that subcategories don't always exist, so let subcategory ID default to NULL as an indicator of no subcategories. For a category with multiple subs, you can use multiple lines (and that lets you personalize the data bits, or have redundancy for caution sake. And process information may go here or in another table, depending..

Similar structure for the LoBs/subLobs, since the sub divisions don't tend to talk to one another.

Then, a 'master' table as it were. It seemed like Regions were fairly data, light, so you can incorporate them here, along with name & ID links to the tables defining more details about the Categories and IDs. If I'm wrong, and you have a fair amount of data about the regions, go ahead and subdivide again like before.

If you have direct linkages between subdivisions as well, potentially. However, it sounded like that data was fairly distinct in your plan.

Hope that helps!

Answer 2

What is the scenario you are trying to design?

Answer 3

Far from an expert, but a few thoughts:

Gather like data together, particularly anything that fits together add a sub-division from another field.
Those sub divisions can be a table too themselves, tied back to their 'parent' field.

Try to write out a diagram of the dependencies, and look for clustering or clear structures (humans are amazing pattern recognition devices)

Some of what you have said translates directly into your column constraints (certain things can't be empty. --> NOT NULL & a DEFAULT value)

Don't let the syntax bog you down now, the db structures are there to express the plan (perhaps with some translation for their limitations), not to define the plan

Answer 4

This might sound pretty simple for experts here but for me am not getting any idea on how to design. I have an excel workbook and need to create database version of it. As overview the workbook consists of different tabs which are different types of "PROCESS". Each tab/sheet ("PROCESS") contains some explanation about "PROCESS" and has many "CATEGORIES". Each "CATEGORY" may or may not have "SUBCATEGORIES". But each CATEGORY and SUBCATEGORY must have some content.

"PROCESS" are related to different "REGION" (data for Region might be North America, Japan, Asia etc) and "LINE OF BUSINESS" (data might be Hardware, Software etc) and "SUB-LOB" (data might be Commercial, Public etc)

In simple terms:
1. Each REGION has many LOB's
2. Each LOB has many SubLOB's
3. PROCESS exists within REGION, LOB and SubLOB. (Some PROCESS have SubLOB and some doesn't)
3. PROCESS has its own content and has many CATEGORIES
4. Each CATEGORY has its own content and may or may not have SUBCATEGORY

Am sorry if it's too much of explanation. I tried my best to make it simple. Hope I get some help here. Thanks.

Answer 5

@Jim the answer and inspiration you gave shows u are expert. since all these concepts are so confusing it's making me hard to visualize. All I can see is many pieces paper that I threw to dustbin pulling my hair. With the logic u said will give another try and to get to the diagram. will see how it goes and meanwhile pls do help me if u have any solutions. Thanks

Answer 6

Thanks Jim for detailed explanation and table relationships. I will try with the approach u suggested. Is there a way I can post image files here? Will create data model diagram from SQL developer and will post screenshot here. so that it will be easier to understand