Results 1 to 9 of 9
  1. #1
    Join Date
    May 2016
    Posts
    6

    Unanswered: is this Database design correct?

    I am making a software for which i have designed this database. Before actually developing, i want to make sure its flaw free..

    Click image for larger version. 

Name:	SHCOM ERD.png 
Views:	19 
Size:	196.4 KB 
ID:	16900

    Here's the design. are the relationships all ok? do i need to change anything?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you redraw the design? I see relationships that start or end on another relationship. They should connect entities.
    Your ER-tool did a terrible job at drawing the relationships, they look like a plate of spaghetti, all mixed up. It makes it hard to read. I don't know to what extend your ER-tool allows you to untangle the relationships to make the whole model more readable. It would help us a lot if you could.

    While you are at it
    - Delete the "tbl_" prefix from each table name. Your name is not "human_marc", but simply "Marc" and your dog's name is not "dog_blackie" but "Blackie", we already know the context when we see you or your dog.
    - Name all your table names in plural or in singular, don't mix them. Now you have "student" and "questions". If you want to start a flame war, ask how table names must be named, plural or singular.
    - All the PK's are VARCHAR(4). Is there any particular reason for? I would suggest using INT's (IDENTITY) for the ID's, and reserve those VARCHAR's for business keys, like an account name.
    As in: the PK of a student is 10, his account_name (a VARCHAR(10)) is 'mm558', his FirstName is "Marc" and his LastName is "Masters". The id is only used within the database to relate the tables (PK and FK) and is not exported outside the database (a report will never show "10", only "mm558").

    Can you tell in words what each entity holds and how they relate to each other? In the database I work on, a class can have both multiple students and multiple teachers (typically during exams and practice lessons, two or more teachers are present in one class). But the model may be a simplification for your assignment.
    Last edited by Wim; 05-25-16 at 12:50.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that this is JB_CF notation:

    1. The delta vectors have no dimension indcators
    2. No cardinality limits
    3. The alternates don't make sense (not always enough of them)
    4. Singleton vectors aren't paired or matched

    There may be other problems once these are resolved.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2016
    Posts
    6

    Reply

    Can you please explain in simpler language? its a bit hard to understand... i am a noob.

    thanks!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let's start at the beginning...

    We normally can look at a diagram and determine what type of model it is. There are a couple of dozen common model types, each with a couple of common notations. Your diagram includes elements of five diagram styles that I can identify, but doesn't include any "telltale" that would indicate which of the diagram styles or models you've used. Without understanding which diagram style you're using, I can't tell precisely what the diagram means.

    The whole point of modeling is to convey information from the architect to others on the team. Some of that communication is done using symbols like rectangles and lines. Some of the communication comes from the text (lists and annotations) within the diagram.

    There are a lot of things that can probably be done to make your design more usable, but until I understand that design I can't help you much.

    If Wim's assumption is correct and your model is a simple "Crow's Foot" data model, then please clean up the diagram so that it is easy to read...
    1. Keep the relationship lines from running on top of each other.
    2. Minimize the lines crossing each other.
    3. Try to arrange objects so they're close to their related objects.

    This will make it easier for us to understand your model, which will make it possible for us to offer suggestions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2016
    Posts
    6

    more clear diagram

    I dont know myself which 'style' it is. However, i have based it on my knowledge of database relationships. (One to many, and so on.)

    Here you go, a more clear version of the diagram.Click image for larger version. 

Name:	ERD.png 
Views:	11 
Size:	28.6 KB 
ID:	16903

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a different type of diagram than the first one was. This one is a Logical Model using Crow-Foot notation, the first was a Physical Model with an unknown notation.

    This model is "complete and correct" in the sense that it meets all of the criteria for a simple CF diagram. It will produce a valid schema that will work as the diagram indicates.

    Without problem specifications to compare the diagram against there is no way to know if they match one another... For instance, it seems unlikely that there will never be a second teacher for a given class, but if your specification is written that way then this model will support that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    May 2016
    Posts
    6

    Thank you!

    Quote Originally Posted by Pat Phelan View Post
    This is a different type of diagram than the first one was. This one is a Logical Model using Crow-Foot notation, the first was a Physical Model with an unknown notation.

    This model is "complete and correct" in the sense that it meets all of the criteria for a simple CF diagram. It will produce a valid schema that will work as the diagram indicates.

    Without problem specifications to compare the diagram against there is no way to know if they match one another... For instance, it seems unlikely that there will never be a second teacher for a given class, but if your specification is written that way then this model will support that.

    -PatP
    I will try implementing this model. Thanks a lot Pat! Also, i will change teacher and class relationship one to one.

  9. #9
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    No class id in the teacher field, can't they tie to a class. Why not interleave your tables with teachers and students giving them unique id's and role id's? Also wouldn't it be better to have a password table that ties to the user through a unique id?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •