UML class diagram vs ER database diagram

Question

Hi, I'm a little confused, I'm developing a program, the program consist in two parts, the server and the clients, there are groups, places, messages... stored in the server, and the clients has to connect with it. I've design the use cases diagram, the activity diagrams, and I have design the class diagram too.

The thing is that I want to implement the server in a mysql tables for storing the users, groups, places... users in groups... so I've designed a E-R diagram consisting in 6 tables, but the problem is that I think that my class diagram and my ER diagram looks too similar, I mean, I think I'm not doing things right because I have a class for each table practically, and when I have to extract all the users on my system, do I have to convert all the rows into objects at first and write in the database for each object modified?

The easy choice for me would be to base my whole application only in the database, and making a class to extract and insert data in it, but I have to follow the UML specification and I'm a little confused what to do with the class diagram, because the books I have read say that I have to create a class for each "entity" of my program.

Sorry for my bad English.

Thank you.

Answer 1

Hello,

I've solved this problem a few times before and I have solved it in this way:

1.) Select (inherited from Vector, it's storing all the rows with standard classes, depending on the language you are working in) Structure:

ID (and ID useful to identify your select) (all the elements of the Select, which is a child of your Vector class are rows)

rowNumber: int

columnNumber: int

usedFilter: String

usedGroupBy: String

usedHavingCaluse: String

usedOrderBy: String

tableName: String

getID()

getRowNumber(): int

getColumnNumber(): int

getUsedFilter(): String

getUsedGroupBy(): String

getUsedHavingClause(): String

getUsedOrderBy(): String

getTableName(): String

Select(tableName: String, filter:String, groupBy: String, havingCaluse: String, orderBy: String, columns: Vector)

2.) I had a class for direct communication with the database, let's call it DataAccessLayer. Let's see the structure of this class:

DataAccessLayer

connect(...): boolean

disconnect(...): boolean

use(databaseName: String): boolean

selectedData: Vector (in fact this is a set of Select's)

createSelect(tableName: String, filter: String, groupBy: String, havingCaluse: String, orderBy: String, columns: Vector): boolean (to determine if it's successful)

deleteSelect(ID): boolean

insert(tableName: String, columns: Vector, values: Vector): boolean

update(tableName: String, columnsToSet: Vector, values: Vector, filter: String): boolean

delete(tableName: String, filter: String): int (how many rows were deleted, -1 if Exception occured, or just simply throw the Exception to a higher level)

//creating/dropping tables/views/databases/constraints can be implemented too, I'm just //lazy to do that, because I'm sure you already understand the idea

DataAccessLayer()

After the first two steps occured, you can handle any database query with two classes (in fact the functionality of Select can be put into DataAccessLayer too, making one class of the two, but it's more elegant this way), but, you might want to handle some extra things for a few tables. The solution is simple, whenever you find a table difficult to use with these, you just have to inherit from DataAccessLayer and redefine what you want to redefine, so in DataAccessLayer you should only use protected and public modifiers and forget the private modifier. So, the relations will be:

Select 1 <-> n DataAccessLayer

ClassInheritedFromDataAccessLayer extends DataAccessLayer

Frontend uses ClassInheritedFromDataAccessLayer1, ..., ClassInheritedFromDataAccessLayern, DataAccessLayer.

This way your project will be: - managable - ordered - easy to plan - easy to implement - easy to modify - easily understood by other people

I hope this helps,

Regards.