a database

[arian]

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

[anduril462]

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.

[arian]

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

[anduril462]

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.

[arian]

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.

[anduril462]

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.

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.

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.

[Elysia]

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).

[arian]

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

[Elysia]

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.

[arian]

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

[Elysia]

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.

[arian]

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

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

[Elysia]

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.

[arian]

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.