• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Database design

I am designing a new database in SQL 2000.  There will be GrandParent who will have Parents.  GrandParents will have access to all the Parents and GrandChildren.  Parents can have Children too.  Parents will have access to only their Children.  Children will have access to only themselves.

for example:  ABC company (GrandParent) have 10 locations.  ABC assigned 5 different logins to five managers (Parents) who are responsible for 2 locations each.  Each manager also assign (1-3) logins (Child) to each location.  Child is only responsible for their locations.

I am doing this project in asp.net/vb.net.

Tables will have these fields:

username
password
FirstName
LastName
CompanyName
Address
City
State
Zip
Phone1
Phone2
fax
email
no of locations
location

If you like clarification feel free to ask.

0
wrathofkhan007
Asked:
wrathofkhan007
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Kevin HillSr. SQL Server DBACommented:
Hello wrathofkhan007...and welcome to EE.

What is your question?
0
 
Craig YellickDatabase ArchitectCommented:
Two database design options comes to mind:

* Three separate tables with traditional primary key - foreign key relationships.

*  A single table with a self-relationship.

Questions:

1. Do individuals in all three classifications share all of the same attributes?  That is, you want to keep the same details for children, parents and grandparents? Or, does each classification carry a different set of attributes and there's little overlap between them?  For example, children have a School attribute that's never used by Parents or Grandparents. Parents have a Work attribute never used by Children, etc.

2. Do you anticipate the need to treat all individuals the same, disregarding their classification? If so, is this a regular and constant thing in your application or a rare, unusual thing?

3. Do you anticipate the need to add additional levels over time? For example, GreatGrandparents or perhaps Pets controlled by Children?

4. Are the individualys *always* in a balanced heirarchy? That is, all Children have exactly one Parent, all Parents have exactly one GrandParent?  All Parents have at least one Child?  All GrandParents have at least one Parent?  There are no circumstances where a Child will not have a Parent and instead be associated directly with a GrandParent?

My gut instinct is to put all of the individuals into the same table and use a self-relationship. This is an extremely flexible design and allows you to handle every conceivable problem that might occur from questions #3 and #4.  The three-table design will cause you untold misery if you ever need to break the rules mentioned in #4, or need to add many additional levels per #3.

The problem wit h the single-table design is that it can be challenging to write queries against it, especially if you are new to self-join concepts. However, that's what EE is for!  These sort of queries are actually fun to work on, when you understand them.

If you go with the three-table design you can overcome question #2 with use of the UNION operator, and create a view that puts all the tables together to form what appears to be a common single table.

The sole advantage of the three-table approach occurs when, per question #1, the classifications have wildly different attributes. You can design each table to store exactly the right data and easily impose the right data integrity rules. When stored in a single table, all individuals will appear to have the same combined collection of attributes, making it more challenging to validate data. Use of views can help make sense of all of this.

This is a lot to digest. Post any follow-up questions you have, and once you decide on an approach, EE can help refine it.

-- Craig Yellick
0
 
wrathofkhan007Author Commented:
I have been leaning towards single table option.

All the attributes are the same for children, parent and grandparent.  It is a possibility that there is only grandparent without any parent or children but parent if present will have grandparent and children if present will have parent and grandparent.

Grandparent  - ABC
Parent - ABC -1
Parent - ABC -2
Parent - ABC -3
Parent - ABC - 4
Parent - ABC - 5 and so on.
Child  - ABC - 11  Parent - ABC -1
Child - ABC - 12  Parent - ABC -1 and so on.

I was thinking about assigning a locationID for the Grandparent as 10-111-0000,Parents as 10-111-1000, 10-111-2000,10-111-3000 and so on.  Children will have locationID as 10-111-1001 - 1999 where parent is 10-111-1000 and 10-10-2001-2999 where parent is 10-111-2000. so I can run my queries for grandparent like '10-111-%', parent like '10-111-1%'.




0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
jrb1Commented:
Sorry, but not good DB design.  You should really look into the 1st 3 normal forms.  And after that, look at Boyce-Codd normal form.

Many links available, but here's one.

http://www.prestwood.com/community/database/info/normalize.asp

LocationTbl
Location
Company
Address
City
State
Zip
Phone1
Phone2
fax
email

CompanyTbl
Company
CompanyName

Person
FirstName
LastName
username
password
Location
Supervisor

Number of locations is a function of the number of rows in the table...no need to store the value.  This is just a first pass at the data, but you really need something more like this than a single table.
0
 
wrathofkhan007Author Commented:
Will this desgin work if a person manages more than one location?
0
 
wrathofkhan007Author Commented:
and also company manages all the locations?

Location
LocationID
CompanyID
Company
Address
City
State
Zip
Phone1
Phone2
fax
email


Company
CompanyID
CompanyName

Person/User
UserID
LocationID
CompanyID
FirstName
LastName
username
password
Supervisor


0
 
jrb1Commented:
Your database design just records information and relationships.  Normalization is a tried method for developing this design (30+ years).  You can program, create views, etc. to determine who controls what.  Even if the controls change down the road, your db design won't need to change.  You change how the data is used.

You have companies, locations, and people. How do they relate to each other?  Each location is associated with a company. Each location is controlled by one person.  Each person does/may report to another person.  Not sure if there's anything else, but once you've defined all that, you'll know how tables should be built.
0
 
Craig YellickDatabase ArchitectCommented:
>> assigning a locationID for the Grandparent as 10-111-0000

You'll regret trying to make the ID value significant. The ideal ID value is meaningless, just a unique number. That way it's never wrong or in need of changing, and since you never need to change it you never need to fix up all the other references that might be stored in other tables.

>> run my queries for grandparent like '10-111-%'

That's the temptation for using an intelligent ID value, and exactly what I meant when I wrote, "it can be challenging to write queries against [single table design]". Once you master the use of self-joins and sub-queries you won't need an intelligent ID and maintenance will be much, much easier.

In a single-table design, a grandparent (or more generically, a top-level node) is a record that does not reference another node. So you can find them very easily.  A child (or more generically, a leaf-level node) is a record that is not referenced by any other nodes.  Mid-level parent nodes are a bit more challenging, they need to both refer and be referred. The queries can get tricky but they are all do-able and don't rely on intelligent ID values.

>> Companies and Locations

That part looks fine, you have a separate table for each, a unique ID in each table, and a reference to a Company in the Location table.

>> a person manages more than one location

Thankfully this characteristic is (from what I can see of your design) independent of the kind of person -- grandparent, parent or child. You might say "a child would never manage a location" but that's just a business rule and not a design issue.  A one-to-many relationship involves adding an additional table to hold the relationship. You already have the Location and Person tables, each with a primary key, so the new table needs those two values along with any other attributes about that relationship.

   LocationManager:  LocationID, PersonID, EffectiveDate, PhoneExtension

Above, I added two attributes as an example. The two ID values are the minimum requirments, indicating that LocationID=101 is managed by PersonID=22 for example.  The EffectiveDate and PhoneExtension are examples of attributes that are *not* about the person and *not* about the location, but are instead about the combination of one person and one location.

>>  Person versus User

You'll regret switching between terms. Stick with one and use it exclusively. Also, once you decide on a table name, always keep the primary key column the same as the table name. The Person table should have PersonID, and if you call it User, then UserID.  The column name should then be used *everywhere* else, always. Otherwise all future queries will be more difficult to write and understand.

>> SupervisorPersonID vs ManagerPersonID vs ReportToPersonID

It sounds like a trivial matter right now, but this is one of the more important column names in your entire single-table relationship design. What, exactly, are we recording?  The terms Supervisor and Manager have implications and assumptions in most organizations, and are not interchangeable.  You need a column name that indicates a relationship without characterizing it incorrectly.  The column name has to make sense for all levels in the heirarchy.  The column will be empty (null) for top-level grandparent people, and will contain the PersonID of their manager/supervisor/boss/foreman etc.

-- Craig Yellick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now