Is it possible to have a list as an attribute of a table in a relational database?

Asked

Viewed 88 times

3

I’m doing a Java project using Sqlite as a relational database. Basically the project is a database of passwords, where a User can have several registered platforms and each platform having its respective Login.

Below is how the class is implemented User:

public class User {

    private Integer id;
    private String name;
    private String email;
    private Login login;
    private List<Platform> platforms;

    (...)
}

The question is how I can store in the bank the list of platforms whose user has an account. If this is possible, or if I will have to resort to a nonrelational bank, for example.

java sqlite relational-model

  • Have you ever thought about using a separate table and defining a foreign key?

  • As I recall, Sqlite is compatible with Mysql, the latter has the JSON type that can be used to store objects and lists. But in SQL it is more common (and most often better) to use foreign keys to connect different tables

  • Answering the first question: Yes, I defined three tables - User, Platform and Login, where I refer the last two as a foreign key in the User table. Perhaps my problem is my ignorance of the application of the concept of cardinality in a database.

  • Responding the second: Interesting! Thanks for the tip. I’m going to do a search about the JSON type in Mysql, and it seems to me that it might even solve my problem. The ideal way I imagined it would be to just store it as JSON, and I thought it would only be in a non-relational database.

1 answer

2


It is not possible to do this in a natural way. The most common is that a list is another table doing the correct one normalization and relationship among them (see more).

In some cases normalization may not be necessary (you have to analyze the context, think of all the details and needs) and then it may be interesting to record everything in the table even simulating a list in a normal column, probably varchar, but you have to handle how to do this.

This is a way of doing something nonrelational in relational, you don’t need to use a wrong database for almost everything you need because of one small detail, which is what a lot of people do nowadays.

Sqlite has nothing of its own because its goal is to be simple and flexible, so it can do everything on it, but it’s not always ready, you should try it, for example do it as a JSON (a format that may be too complicated for something so simple). Other more sophisticated banks can handle this in a natural and own way without having to leave the relational model.

It might complicate the fact that you have to record a complex type (Platform) since you need to deal with it as well, and that goes for many other databases, few have the resources to deal with it well and practically nobody uses it when they do (I don’t know why, if it’s because they see a problem in it). But in the comments it says that there is a table of this type so maybe you just need to put in the list a idparticipants, which may allow everything to be the same size and format as the array simulated in a varchar be very simplified.

I take this opportunity to say that you probably learned the term used mistakenly (unless you’re talking about the database, which it actually fits, although it’s more common for people to use the column for this, which seems more correct).

  • Oops, thank you for the reply and for the content links. I probably used the terms incorrectly, because what I meant by "attribute" was just the column, as you mentioned. Thank you for correcting.

  • It is column in DB (but there is use of attribute), the problem is the use in class, the correct is field, because attribute is something else (it is not your fault almost everyone teaches wrong). Now you can vote for everything in sute too.

Browser other questions tagged java sqlite relational-model

You are not signed in. Login or sign up in order to post.