a database

This is a discussion on a database within the Tech Board forums, part of the Community Boards category; Hi, I have to create a database. The data format is something like the following: A (a1,a2,a3,a4) (for example A=shoe, ...

  1. #1
    Registered User
    Join Date
    Nov 2003
    Posts
    177

    a database

    Hi,

    I have to create a database. The data format is something like the following:

    A (a1,a2,a3,a4) (for example A=shoe, a1=shoe lace, a2=insole , ...)
    B (b1,b2)
    C (c1,c2,c3,c4,c5,c6)
    D (d1,d2,d3,d4,d5)

    The problem is the number of input for each entry may differ from the others (there are 4 entries for 'A', 2 entries for 'B' and .... ) so I cant have a single table format for all of them.
    I was wondering if it is possible to use an access database in which one table includes main entries (A,B,C,D here) while each row is connected to another table containing the sub_data?
    I have attached a picture to make it clearer

    Thank you
    Arian
    Attached Images Attached Images  

  2. #2
    Registered User
    Join Date
    Nov 2010
    Location
    Long Beach, CA
    Posts
    5,676
    Quote Originally Posted by arian View Post
    I was wondering if it is possible to use an access database in which one table includes main entries (A,B,C,D here) while each row is connected to another table containing the sub_data?
    Yes, that's possible.

  3. #3
    Registered User
    Join Date
    Nov 2003
    Posts
    177
    Would you kindly please guide me to do so?

    I know how to connect two tables using the key. The problem is I don't know how to connect each row in a table to a whole new table

  4. #4
    Registered User
    Join Date
    Nov 2010
    Location
    Long Beach, CA
    Posts
    5,676
    Quote Originally Posted by arian View Post
    Would you kindly please guide me to do so?

    I know how to connect two tables using the key. The problem is I don't know how to connect each row in a table to a whole new table
    I might be able to guide you if you could tell me more about the database. Are you using an existing database like MySQL or SQLite, or CouchDB, or going old-school and using something like ISAM? Or are you supposed to code the database yourself? If you want help, you need to give us enough details to actually help you.

  5. #5
    Registered User
    Join Date
    Nov 2003
    Posts
    177
    Thank you for helping me

    I am a rookie in the field of database and have no idea what "ISAM" and "CouchDB" are

    I rather create the database and the needed tables in Microsoft access.
    The main table is mainly a topic table, something like a table of contents!
    and the other tables need to have at least 3 columns, (word, definition and examples ) but the number of rows vary from one table to another.

    I am a medium programmer in c++ and C# and have written some codes to import/export data from/to Microsoft excel.

  6. #6
    Registered User
    Join Date
    Nov 2010
    Location
    Long Beach, CA
    Posts
    5,676
    Ahh, now I see where you said "access database" in your first post. I didn't realize you were naming a specific database versus talking about accessing a database.
    Quote Originally Posted by arian View Post
    I am a rookie in the field of database and have no idea what "ISAM" and "CouchDB" are
    CouchDB and ISAM are names of other database systems, each using different paradigms than relational databases like SQL or Access.
    Quote Originally Posted by arian View Post
    I rather create the database and the needed tables in Microsoft access.
    The main table is mainly a topic table, something like a table of contents!
    and the other tables need to have at least 3 columns, (word, definition and examples ) but the number of rows vary from one table to another.
    I don't know anything about MS Access, but in principle, it should be similar (at least, enough so for your scenario) to a SQL database. The number of rows in any table doesn't matter*. Your main topic table can have as many rows as it needs, one for each topic you have. The other table will require a foreign key -- a column that stores some identifiable information for the topic it relates to. Thus, both "shoe lace" and "insole" will have a key (the same key, that matches a unique key for "shoe" in the topic table) that points back to the topic "shoe". The word/defintion table would also have entries for other topics. So if you had a second topic "animals", the other table would have entries like "dog" and "cat" that both have the same foreign key tying it to the topic "animal".

    I suggest doing some basic reading on relational databases, specifically primary keys and foreign keys, and going through some MS Access tutorials and help sites.

    * Again, I don't know MS Access, but most databases don't put a hard limit on how many rows a table can have by default -- you can have as many as there is space for -- unless you impose such a limit yourself.

  7. #7
    C++まいる!Cをこわせ!
    Join Date
    Oct 2007
    Posts
    22,985
    Keep in mind that you can have "null" entries on columns, so if you have

    A1 B1
    A2 (null)
    A3 B2
    A4 (null)

    you can just store it as that in the database within a single table. In this case, I'd wager it would be easier.

    Consider multiple tables if you need lookup tables or to de-dup a table (i.e. A1 has B1 and B2, hence possibly resulting in two rows: A1 B1 and A1 B2).
    Quote Originally Posted by Adak View Post
    io.h certainly IS included in some modern compilers. It is no longer part of the standard for C, but it is nevertheless, included in the very latest Pelles C versions.
    Quote Originally Posted by Salem View Post
    You mean it's included as a crutch to help ancient programmers limp along without them having to relearn too much.

    Outside of your DOS world, your header file is meaningless.

  8. #8
    Registered User
    Join Date
    Nov 2003
    Posts
    177
    As far as I know there is no limit in the number of rows in Microsoft access as well.

    What I have gotten form your explanation is that all I need are 2 tables: 1- table of content and 2- data table (word, definition and examples ).
    The table of content needs to have 2 columns (key and number)
    The data table contains (key and all the information needed for each word)
    With a correct value for the key and a simple query I can get all the required info related to a word in the table of content.

    Am I right?

    Thanks a million, I can say I am now understanding the meaning of database and queries

  9. #9
    C++まいる!Cをこわせ!
    Join Date
    Oct 2007
    Posts
    22,985
    You should do some research on primary keys and foreign keys. Understand what they are, and how to construct them.
    However, if you keep all data in the same table, you will get less headaches from this since you don't need keys in the first place, so I would do that if I were you.
    Quote Originally Posted by Adak View Post
    io.h certainly IS included in some modern compilers. It is no longer part of the standard for C, but it is nevertheless, included in the very latest Pelles C versions.
    Quote Originally Posted by Salem View Post
    You mean it's included as a crutch to help ancient programmers limp along without them having to relearn too much.

    Outside of your DOS world, your header file is meaningless.

  10. #10
    Registered User
    Join Date
    Nov 2003
    Posts
    177
    You are right

    But if I use one table, the words referring to the main category (ex shoe) should be repeated for all the related words, have I got it correctly?

    And I guess this one would be a good practice for me to start learning about the keys, so I probably will go for that although it will be a harder task

  11. #11
    C++まいる!Cをこわせ!
    Join Date
    Oct 2007
    Posts
    22,985
    I'm lost at your requirements.
    What kind of data do you have exactly, and what are the relationships between them?

    You have words, and you have categories. How are they related?
    How many categories can one word have?
    How many words can one category have?
    Is there anything else?

    A single table works great if
    - There is only one category for each word
    - There is only one word for each category
    - You will not rename categories later

    Otherwise we're going to have get dirty.
    Quote Originally Posted by Adak View Post
    io.h certainly IS included in some modern compilers. It is no longer part of the standard for C, but it is nevertheless, included in the very latest Pelles C versions.
    Quote Originally Posted by Salem View Post
    You mean it's included as a crutch to help ancient programmers limp along without them having to relearn too much.

    Outside of your DOS world, your header file is meaningless.

  12. #12
    Registered User
    Join Date
    Nov 2003
    Posts
    177
    Quote Originally Posted by Elysia View Post
    What kind of data do you have exactly, and what are the relationships between them?

    You have words, and you have categories. How are they related?
    How many categories can one word have?
    How many words can one category have?
    The entries are all words.
    one example is:
    Category= shoe
    Related words= shoe lace, insole and ... any other words/expression related to the word shoe

    and really I cant come up with any exact number

    Quote Originally Posted by Elysia View Post

    A single table works great if
    - There is only one category for each word
    - There is only one word for each category
    - You will not rename categories later
    I guess there are situations in which the criteria isn't met



    Thank you so much for the help,
    I am reading an article about foreign keys

  13. #13
    C++まいる!Cをこわせ!
    Join Date
    Oct 2007
    Posts
    22,985
    Quote Originally Posted by arian View Post
    The entries are all words.
    one example is:
    Category= shoe
    Related words= shoe lace, insole and ... any other words/expression related to the word shoe

    and really I cant come up with any exact number
    We are only really interested in one or many (i.e. more than one).

    From what I can see in your data, you have words, categories and related words.
    When designing a database, one typically separates entities and storage. Entities are description of all your data and storage is a way to store the data in your database.
    So basically entities would be categories and words. Storage would be a table that stores information about what categor(y/ies) a word belongs to and related words or what have you.

    Each entity is usually a table. This is often called a lookup table. It is typically used to reduce the possible selections for a column to a set of predefined values.
    So you might make a table for Words and for Categories. This is not necessary, but it can help in certain situations. Look for what lookup tables can do for you.

    The storage table would probably look something like

    Word Category RelatedWords

    At least, from what I gather from what you've said so far.
    However, if we consider that one word may have many related words, we get duplication, and that is usually when we split the table into a so-called link table.
    If one word has multiple categories, we do the same for that. A link table.

    Assuming one word, one category and one word, multiple related words, this is one possible structure of the database:

    Table: WordsTbl (Storage table)
    Fields: Word (Number, Primary key), Category (Number)

    Table: WordsLTbl (Lookup table)
    Fields: ID (Number, Foreign key/Primary key), Val (String)

    Table: CategoryLTbl (Lookup table)
    Fields: ID (Number, Foreign key/Primary key), Val (String)

    Table: RelatedWordsLnkTbl (Link table)
    Fields: Word (Number), RelatedWord (Number)
    Keys: Word and RelatedWord makes up the primary key/foreign key (yes, it is valid to have multiple columns for a key)

    Then for relations:
    WordsTbl.Word <-- (1:1) --> WordsLTbl.ID. Use WordsLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
    WordsTbl.Category <-- (1:1) --> CategoryLTbl.ID. Use CategoryLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
    WordsLTbl.Word <-- (1:Many) --> RelatedWordsLnkTbl.Word. Use WordsLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
    WordsLTbl.Word <-- (1:Many) --> RelatedWordsLnkTbl.RelatedWord. Use WordsLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).

    Each row in RelatedWordsLnkTbl contains a related word for a word. Several rows with the same word are used to indicate that one word have several related words. E.g.:

    "Word A", "Related Word A"
    "Word A", "Related Word B"
    "Word B", "Related Word C"
    etc

    A primary key and foreign need not be numbers. They can be any indexable data type (e.g. strings). However, if you later wish to change the spelling of a word or a category, it's much easier to use a unique identifier for them instead of directly putting in the string. If you put in the string, then if you change an entry in WordsLTbl, for example, then you need to update WordsTbl with the new word, or the references won't work anymore! Sure, Access can do this for you, but as your database grows larger, this can become a bottleneck. That is why I always use unique identifiers.

    I hope this is enough to get you started. There is a lot of material you need to review in order to understand this.
    Quote Originally Posted by Adak View Post
    io.h certainly IS included in some modern compilers. It is no longer part of the standard for C, but it is nevertheless, included in the very latest Pelles C versions.
    Quote Originally Posted by Salem View Post
    You mean it's included as a crutch to help ancient programmers limp along without them having to relearn too much.

    Outside of your DOS world, your header file is meaningless.

  14. #14
    Registered User
    Join Date
    Nov 2003
    Posts
    177
    I really appreciate all the help and support
    Thank you so much for the time
    I will finish the article and will start working on the database, based on your explanation.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Database to Database Transfer?
    By draggy in forum C++ Programming
    Replies: 4
    Last Post: 01-17-2007, 10:50 AM
  2. Database
    By sreetvert83 in forum C++ Programming
    Replies: 6
    Last Post: 09-14-2005, 11:06 AM
  3. Database
    By JHOOMAN in forum C Programming
    Replies: 1
    Last Post: 04-02-2002, 09:59 AM
  4. Replies: 1
    Last Post: 10-09-2001, 11:20 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21