Need help making DB Schema

Question

To make it short, I need help making a nice DB Schema, and I'm not well versed in the way of databases.

I can give you a sample of the data I want to be storing, in a json format.

"items": [
{
"defindex": 45,
"level": 10,
"quality": 6,
"quantity": 1,
},
{
"defindex": 348,
"level": 10,
"quality": 6,
"quantity": 1,
},

There would be many more of these though, up to 1000, all attached to an identifying number, so I could say "5162562 has these items". However, I also want to be able to the search the database for "defindex" = 45, and then get "5162562", or all identifying numbers that have a "defindex" = 45 in there items stored.

There will be a large magnitude of data stored, so I'm wondering too what DB to use, MySQL?

Thank you so much, I'm new to SQL databases, but not to programming, so I'll hopefully be able to understand.

-Billy

score 0 · Answer 1 · answered Aug 14 '12 at 22:20

MySQL is great if you are planning on joining. However, if you simply have a tree like relationship, and only have to traverse down the tree (parent to child) or by siblings (child to other child), then MongoDB or other NoSQL database solutions such as Riak will be faster and more flexible to deploy. As a plus, NoSQL databases will accept slightly modified JSON.

In your situation, this looks like something that is perfect for NoSQL.

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

BTW, I hope you've correctly tagged this as java - your code is javascript (although a format used often in Java).

If you learn how to use the Java Persistence API (with an implementation like Hibernate EntityManager), you won't need to create the schema manually, as it will be automatically created from the annotations on your Javabeans and your choice of DB.

A tutorial from Oracle.

If you're using Lombok as well, your class might be as simple as

@Getter @Setter
@Entity
public class MyEntity {
    @Id private int id;
    @Column private int original_id;
    @Column private defindex;
    @Column private int level;
    @Column private int quality;
    @Column private int inventory;
    @Column private int quantity;
    @Column private int origin;
}

or you could define enums for the field and use those instead.

Watch out for The JPA hashCode() / equals() dilemma

edited May 23 '17 at 12:04

Community

1
1

answered Aug 14 '12 at 22:20

fommil

5,757
8
41
81

Thank you! I did correctly tag it as Java. Do you know how well the built in persistence api would scale with 1000's of data entries? – Billy Ryan Aug 14 '12 at 22:29
lol - thousands is **not** big. You could easily use the built-in database JavaDB (previously called Derby) for that in "embedded" mode. If you need millions, that's when you might want to start looking at a faster SQL server, with indexes (which can be set up with Hibernate specific annotations, btw). For you data, which might have a `@OneToMany` collection in it, from re-reading, I'd suggest you investigate the `mappedBy` annotation option and if performance is a problem, build the index on it. – fommil Aug 14 '12 at 22:32
Ok, let me rephrase then. Theoretically, it could contain 9999999999000 items. Now it won't ever be that high, would it still be fast at a fraction of that? – Billy Ryan Aug 14 '12 at 22:34
well that's another extreme. Maybe you'd be best thinking about how many entries there will actually be. Try the simplest approach first, and then ask again if it is too slow. – fommil Aug 14 '12 at 22:37
If you have no idea how many entries there will be, then I strongly agree with fommil. Go with something simple to start with and then solve the scaling problem if and when it crops up. huadianz's comment below is spot on in that there are NoSQL solutions that would better fit what you've described (and offer excellent scaling). – Andrew Aug 14 '12 at 23:36
How can you not guess order of magnitude? – fommil Aug 15 '12 at 06:50

Need help making DB Schema

2 Answers2