Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Db design question (2003)

    I am just starting to design a new database to store and manage data for letters of credit.

    At the moment I have four tables: tblCompany; tblDocsEnclosed; tblLCData; tblInstructions.

    The nature of the letters of credit are such, that each application for a company will have their own specific set of instructions. I would like the user to select the specific instructions they need for that application which will be printed on a report later.

    In the tblCompany table I have a field called InstructionsID (Data Type = Number), which I'm hoping will contain an array type list of selected items from the tblInstructions table.

    Is this going to be possible?
    Is there a better way to do this?

    I hope I have explained myself well enough.

    Many thanks as always
    Hayden

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Db design question (2003)

    You shouldn't store multiple values in a field. Instead, use an intermediate table that joins the tblCompany and tblInstructions tables. The intermediate table would have fields CompanyID (linked to the ID field in tblCompany) and InstructionID (linked to the ID field in tblInstructions); the primary key of this table would be the combination of these two fields. This means that each CompanyID can occur many times in the table, as can each InstructionID, but the combination would be unique.

    By the way, are you sure that the instructions should be linked directly to the company? Shouldn't the instructions be linked to the credit letter?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Db design question (2003)

    Are you meaning a many to many relationship between the company and instructions? (this scares me a bit)

    I have thought that I need to include the instructions with the company, because at the moment they are done on an excel spreadsheet template. Each company has it's own template with the instructions required for their application already set up.

    If I were to link the instructions to the credit letter as you suggest would I get away with not having to include an intermediate table?

    Kind regards
    Hayden

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Db design question (2003)

    Here is my current relationships

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Db design question (2003)

    If the instructions are set up for a specific company, not for a specific letter of credit, the setup as I described, and as you have in the relationships picture you attached, is the one you need. It is indeed a many-to-many relationship but you needn't be afraid of that. An intermediate table to implement a many-to-many relationship may seem intimidating, but in the end it will make designing the database MUCH easier than trying to cram multiple values into one field.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Db design question (2003)

    Thanks Hans

    Do you think that, from what you can see from the relationships, it looks OK to move forward with?

    Also, I know that in many cases design, not just of databases, but design in general is very subjective. A case of "beauty is in the eye of the beholder" But would you know of any links to existing databases who, in your opinion, manages to combine, functionality with pleasing form layouts etc.? Do you have any tips on form design?

    Many thanks for all your help
    Hayden

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Db design question (2003)

    I don't understand the setup of the relationships between tblCompany, tblLCData and tblDocsEnclosed. Both tblLCData and tblDocsEnclosed contain LCID and CompanyID fields; the triangle relationship can cause problems (as in real life) - records might not be updatable.

    As you say, design is subjective. Here are my preferences:
    <UL><LI>Keep things simple.
    <LI>Don't try to cram too much information on one screen. Use a tab control to divide the information into logical units.
    <LI>Use the Windows system colors where possible, and other colors sparingly. Avoid garish colors.
    <LI>Be consistent - don't apply completely different designs to different forms.
    <LI>(This is personal) Don't use pictures as background to forms (as in most of the AutoFomat options provided by Microsoft) - they take up lots of space and I don't want to be staring at a globe or some such all the time.[/list]If you find a pleasing design, stick with it.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Db design question (2003)

    Thanks for the form tips Hans,

    I think I see what you mean about the triangle relationship.

    What I'm trying to set out is this:

    The tblLCData is going to be my main table for each letter of credit application received from a company.

    With each application, certain documents are required to be attached before sending the whole pile to the bank. The quantity and type of document required to be attached to the application cannot be preset. What I thought, was to hold the possible type of documents required in a separate table, that way when I enter the application data into tblLCData I can then select the type of document I need from the tblDocsEnclosed table and stipulate the number of each type of documents attached.

    You know as I'm writing this I'm thinking that it is not making sense... I'm going to have to have a many to many relationship between the tblLCData and the tdlDocsEnclosed data as well aren't I? but then what would the primary key be in the tblDocsEnclosed?

    Sorry Hans now I'm really getting myself all confused.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Db design question (2003)

    It's probably best to write out the data items you need on paper, and to work out the logical units (tables) and their relationships before trying to design the actual table structure. If you start on the tables right away, you are likely to be sidetracked by technical details. Getting a clear understanding of the data structure is very important. Many of the technical details will follow logically from that.

  10. #10
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Db design question (2003)

    Hi Hans

    Sorry, I was away the end of last week, thank you for the advice.

    Kind Regards
    Hayden

Posting Permissions

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