dcsimg
Results 1 to 18 of 18

Thread: Database design

  1. #1

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Database design

    Hi,

    I am thinking to create this project but first I wish to get some ideas of direction I must take.

    In this picture below (that I create in excel for example) I will have a context of an assessment. Very simple. I shall call this table tblContext

    Name:  1.JPG
Views: 151
Size:  13.8 KB

    Next, and this is where I am not sure...

    Name:  2.jpg
Views: 150
Size:  11.8 KB

    In this table yellow parts is fixed. Meaning for every assessment the user shall see that. The only parts that user shall complete every time is blue parts. If person clicks on block it shall make "x" if person click again "x" is gone. (but that I can think later, for now I wish to decide on database structure first)

    What is best way to go about?

    Thanks!!!
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,847

    Re: Database design

    Since you say all records will have have the same data, you only really need one record for each Assessment.

    Name, Assmtdate, Approved, Verified85, Verified85100, Verified100, Tested85, Tested85100 ....... (and a primary key field)

    But I really don't like that, it's a very large record and leaves you with very little flexibility if something changes in the future.

    You could have a simple One to Many database with a Context table and a ContextDetails table. Each Context record would have three Details records associated with it, (85db, 85-100db and 100db).

    You really need to think towards the future before decide. Only you know or can guess who this program might morph into in the future.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,634

    Re: Database design

    Database design is not a VB.NET issue. This site has a Database Development forum so I've asked the mods to move this thread there.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,634

    Re: Database design

    I agree with wes4dbt. Right now, one table is probably the way to go but if there's a reasonable chance that those noise ranges could change then you might want to split things up into multiple table. For instance, you might have a NoiseRange table with Minimum and Maximum columns, an Assessment table that has a foreign key to NoiseRange and another to Context, three Boolean columns and a text column.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Re: Database design

    Hello Wes

    I was think maybe like so? (This assessment structure I don't think will ever change...) Maybe headings and so but not the manner how it is done..

    Name:  3.JPG
Views: 69
Size:  14.6 KB
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  6. #6

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Re: Database design

    Hi John,

    Okay, thanks
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,634

    Re: Database design

    Quote Originally Posted by schoemr View Post
    Hello Wes

    I was think maybe like so? (This assessment structure I don't think will ever change...) Maybe headings and so but not the manner how it is done..

    Name:  3.JPG
Views: 69
Size:  14.6 KB
    Definitely not. There's no good reason to have three tables with the same schema that contains basically the same information. Data like that should all be combined into a single table with a foreign key to another table that has three rows. You ought to do some reading on database design and normalisation.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Re: Database design

    Quote Originally Posted by jmcilhinney View Post
    Definitely not. There's no good reason to have three tables with the same schema that contains basically the same information. Data like that should all be combined into a single table with a foreign key to another table that has three rows. You ought to do some reading on database design and normalisation.
    John, this is what I was have in mind. That three rows you see is actually 3 separate datagridviews.. Is it bad idea?

    Name:  4.jpg
Views: 69
Size:  15.0 KB
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,349

    Re: Database design

    Thread moved to the 'Database Development' forum


    Quote Originally Posted by schoemr View Post
    John, this is what I was have in mind. That three rows you see is actually 3 separate datagridviews.. Is it bad idea?
    Why do you want to put in that extra effort?

    One table is all that is needed to store the values, so one DGV would easily do the job of displaying them.

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,534

    Re: Database design

    Quote Originally Posted by schoemr View Post
    Hi,

    I am thinking to create this project but first I wish to get some ideas of direction I must take.

    In this picture below (that I create in excel for example) I will have a context of an assessment. Very simple. I shall call this table tblContext

    Name:  1.JPG
Views: 151
Size:  13.8 KB

    Next, and this is where I am not sure...

    Name:  2.jpg
Views: 150
Size:  11.8 KB

    In this table yellow parts is fixed. Meaning for every assessment the user shall see that. The only parts that user shall complete every time is blue parts. If person clicks on block it shall make "x" if person click again "x" is gone. (but that I can think later, for now I wish to decide on database structure first)

    What is best way to go about?

    Thanks!!!
    A bit of nitpicking:
    you do realize that "Noise<100db" (3rd row) doesn't make sense in context of "noise<85db" etc.
    Should probably be "Noise>100db"
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Re: Database design

    Quote Originally Posted by Zvoni View Post
    A bit of nitpicking:
    you do realize that "Noise<100db" (3rd row) doesn't make sense in context of "noise<85db" etc.
    Should probably be "Noise>100db"
    hi Zvoni,

    Just a typo But thank you for pointing it out
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  12. #12

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Re: Database design

    Quote Originally Posted by si_the_geek View Post
    Thread moved to the 'Database Development' forum


    Why do you want to put in that extra effort?

    One table is all that is needed to store the values, so one DGV would easily do the job of displaying them.
    Hi SI,

    That is why I was first post here. For this one I don't want to rush into something like I have done many times now and then later I just abandon the project because I did not start right and make stupid mistakes. For this one I want to rather spend lots of time thinking about it first before I start do it...

    I am not really in big rush so I can take my time to think about it and also to get some experts guidance here on vbforums

    Also, my idea in post #8 .. I realizes it is certainty not the best way or the most clever way, but will it work?

    JMC help me before with a survey. He also made a sample project (for which I was very grateful) To this day I am still looking at that to try and figure out how he did it. I have try maybe to be honest 20 times to mimic that without success. I am say this to show that I am still learning and sometimes things are just too high for me in complexity because I am not there yet with knowledge.
    Last edited by schoemr; Dec 3rd, 2018 at 08:19 AM.
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,847

    Re: Database design

    Also, my idea in post #8 .. I realizes it is certainty not the best way or the most clever way, but will it work?
    lol, you realize it's not the best way, jmc and si tells you not to do it that way, in post #1 you ask for advice , but you still want to know if it would work. ????

  14. #14

    Thread Starter
    Fanatic Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    827

    Re: Database design

    Wes... I don't see any harm in asking a question...
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,349

    Re: Database design

    If you wanted to walk to a point across the room you could walk around the entire house first, then come back into the room and go to the point to you wanted to get to... or you could just walk directly to the point you want to get to.

    While your idea would work, it is just adding extra work on top of our suggestions, and is not making anything easier for you.

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Database design

    I don't see any harm in asking a question
    Actually, I agree, and I wouldn't want to discourage you from suggesting your own ideas. They may be right, they may be wrong, but they will always offer you a chance to learn. However in this case you've got a several experienced DB professionals all giving the same answer. That's probably a clue that the answer they're giving you is the right one.

    The argument against your design in post 5 boils down to "it's not giving you any benefit but it's costing you something". By representing each noise range in a separate table you're going to introduce the need to join them back together later when you want to see an assessment.

    The only potential benefit I can see to that design is that you could add different ranges later by adding different tables. But you said up front "In this table yellow parts is fixed" which we're taking to mean the ranges are fixed. If you do need to add different ranges then the single table approach we're suggesting is not suitable but there are still better designs than the one you've suggested. If you do need to explore that then let us know and we'll start suggesting some more flexible alternatives.

    Other than that, is there some benefit that you think your design is giving you that we're missing? The chances are we can suggest a better alternative that will achieve those benefits.
    Last edited by FunkyDexter; Dec 5th, 2018 at 03:18 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  17. #17
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,078

    Re: Database design

    I'm wondering, what is the problem, this is supposed to be a solution for
    do not put off till tomorrow what you can put off forever

  18. #18
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,312

    Re: Database design

    The thing with db desing is that there are many ways to complete something. Some have advantages over others (eg speed or easiness to see or flexibility).

    I would advise that you try as many methods as possible so you can understand why some people suggest certain ways to do things. Also it allows you to explain your reasoning as to why you decided to do it in a particular way.
    So, since its not urgent, try them all. Some will take more time to do but you can get more of an insight how to complete things.

    Also, as has already been posted, read up on normalization. You dont need to take it to the 5th level, 2nd or 3rd is usually enough to allow you to try out different ideas.

    This is just an example, and perhaps you can build it to try...
    table of Test requirements
    testid pk auto
    testname text
    statusid int fk (points to a teststatus table)
    dtsubmitted date
    usrsubmitted string // or usrsubmittedid int (points to user table)

    Results Table
    ResultID pk auto
    ResultTypeID fk int (points to table of type of test actioned, <85db, 85-100db, etc plus any other types
    Verified boolean
    Tested boolean
    reported booean
    action text

    The other tables I've mentioned are the look up type tables you'd need to create.
    As I said, this just an example of a way to do this. Try as many as you can to see pros and cons and which controls on forms work better with the data structures...

    Any problems, people here can help point you in the (working/correct?) right direction

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width