first time dealing with databases, need help

[Leros]

I'm creating a simple Android application where a user can subscribe 'inventories' and then view the 'items' within each inventory. I want to persist the list of inventories and the items in each inventory so I won't need to go out to the server to view the information every time.

I've never done any work with SQL or any database system. I have looked at a few simple Android SQLite examples and understand the basic idea. These examples have a few hardcoded hard coded tables in the database, simpler than the behavior I want (I think... ).

My thinking is that I want one table for the list of inventories. Then when I subscribe to a new inventory, I'll dynamically add a new table for the items in that inventory. This is where I get lost.

Can I create tables on the fly?

Assuming I can, I'm guessing that one of the columns in my "inventory list" table will be a pointer to the "items" table for that inventory. Does that sound right?

Please advise. I could use any help or pointers I can get.

 

[tatteredpotato]

I'm no SQL expert, but you can issue an sql query to create tables/databases on the fly. The concept of having a "pointer" to another table is known as a "foreign key" in sql.

 

[beginner99]

In General Creating/dropping a table on the fly means something is seriously flawed in your architecture. IMHO.

Just create a table with following columns:

- inventoryID -> this links to your inventory list table; unique identifier for your "inventory"
- itemID -> unique identifier for each item
- all the columns you need to describe an item

If a user now adds an inventory it just puts all the items from it into this existing table with the according inventoryID. If a user removes an inventory, all of them are delete, eg.
DELETE FROM InventorItem WHERE inventoryID = 32; (I just randomly picked a number, 32 has no further meaning)

 

[Leros]

In General Creating/dropping a table on the fly means something is seriously flawed in your architecture. IMHO.

Just create a table with following columns:

- inventoryID -> this links to your inventory list table; unique identifier for your "inventory"
- itemID -> unique identifier for each item
- all the columns you need to describe an item

If a user now adds an inventory it just puts all the items from it into this existing table with the according inventoryID. If a user removes an inventory, all of them are delete, eg.
DELETE FROM InventorItem WHERE inventoryID = 32; (I just randomly picked a number, 32 has no further meaning)

Hmm, so its a better design to have one big table with all of the items for all inventories?

It seems like it would be more efficient to separate your data logically into separate tables. If I keep a big table, I'm going to have to perform a query on the entire set of data for all inventories to get the data for one inventory. Whereas, keeping individual tables, I would just have to do a constant lookup of the table I need and then iterate through the small table.

 

[aceO07]

I recommend having 2 tables, inventories [id, name] and items [id, name, inventories_id].

Each time you want to add an item to an inventory, create an item that has the inventories_id field to match the inventory record's id.

To find all the items for an inventory, just do "SELECT * from items where inventories_id = [inventory id]". Then you can iterate through the results.

With just these 2 tables, you can do things like searching for all the items with a specific name. Imagine trying to do that if you separated out the items by inventory into their own tables. Or trying to count the number of items in each inventory (without having to go through each table).

Databases are very efficient, so you don't need to worry too much about optimizing them until you deal with very large amount of data. Even then there are simple ways to make it faster until you get to huge amounts of data.

 

[beginner99]

Hmm, so its a better design to have one big table with all of the items for all inventories?

It seems like it would be more efficient to separate your data logically into separate tables. If I keep a big table, I'm going to have to perform a query on the entire set of data for all inventories to get the data for one inventory. Whereas, keeping individual tables, I would just have to do a constant lookup of the table I need and then iterate through the small table.

Your post basically says, that you need to read/inform yourself about relational databases and SQL. And yes it is more efficient. It is exactly the purpose of a relational database.
They can deal with millions of rows. Ok, you will run it on a mobile phone but still, I don't think 100k rows will be a big issue.

As you can see someone else proposed the exact same thing as I did.
(If the same item can appear in multiple inventories, the structure would have to be made a little more complex)

Again, I don't want to explain anything but if you inform yourself about relational databases and SQL you will understand why it is the way to go as I suggested.

You can think of it in this way simplified:
A table should only contain "Objects" of one type. And there should always only be one table for a certain type of "Object".

 

[Nothinman]

Hmm, so its a better design to have one big table with all of the items for all inventories?

It seems like it would be more efficient to separate your data logically into separate tables. If I keep a big table, I'm going to have to perform a query on the entire set of data for all inventories to get the data for one inventory. Whereas, keeping individual tables, I would just have to do a constant lookup of the table I need and then iterate through the small table.

But if you have 1 table per inventory type then you have to enumerate the inventory types, then perform your queries on that table. With just 2 tables you would have 1 query that returned all of the inventory and which type they are with a simple join. To show all inventories of a certain type you would just need a SELECT with a WHERE clause.

 

[Leros]

Thanks for the help. Two tables makes a lot of sense.