0

Hello I am currently developing a small website for a real estate and I want to design a database for it. I have implemented user sign-up but I want the user to select one or more roles. To be more specific a user can be someone who can sell or buy houses or both. So for each user-type I want to have a table with its properties. My problem is that I don't know how to relate the users with attributes. So far I have these tables:

User:
varchar userid;
varchar password;
varchar registrationDate;

Buyer:
varchar id;
varchar housesBought;

Seller
varchar id;
varchar rating;
varchar housesSold;

Somehow I need to connect these entities but so far I cannot find any good practice. Any guideline or solution would be appreciated. Thx in advance

Eugene Podskal
  • 10,270
  • 5
  • 31
  • 53
JmRag
  • 1,443
  • 7
  • 19
  • 56

3 Answers3

2

You don't say what database you are using, but I'll guess maybe it's MySql? Anyway, the following principles will apply to almost any relational database package (MS Access, SQL Server, PostGres etc)

For any table that has a relationship with the user table, you need to have a "foreign key", which is a field that relates back to another table. For example, taking your design:

User
int id;
varchar user_type;
varchar password;
varchar registrationDate;

Buyer:
int id;
int user_id;
varchar housesBought;

Seller
int id;
int user_id;
varchar rating;
varchar housesSold;

Note that I've changed the primary keys (id fields) to integers. You should not use varchar for numerical data, as its very inefficient when it comes to indexing.

The user_id fields link to the id field of the User table.

However, I would actually recommend a different design altogether. Your design, with separate tables for buyers and sellers attributes has some limitations. For example, what if someone is both a buyer and a seller - then you have data stored in two different places, which is unnecessary in this simple example. Also, the relationship that we have defined between the user table and the other two tables is a one-to-one relationship. For any given user, you'll always have only one record in each table. This implies that you might be better just storing all the information in one table.

Generally in relational database design, we use 1-to-many relationships as an efficient way to partition and link data. For example, let's say you have a table storing addresses for your users. Each user could have more than one address, so we have something like this:

Address
int id;
int user_id;
bool is_default_address;
varchar address_line1;
varchar address_line2;
varchar county;
varchar postcode;

Because we can have many addresses per one user, this is therefore a one-to-many relationship, using the foreign key user_id to link the tables.

Now, my final recommendation is to overhaul your database design to get away from those 1-to-1 relationships. I suggest you go for something like this:

User:
int userid;
varchar password;
varchar registrationDate;
int rating;
int housesSold;
int housesBought;


Property
int id;
int seller_id;
int buyer_id;
varchar description;
varchar address;
varchar postcode;
decimal price;

Now you have all user details stored in one table - yes there's a small amount of redundancy, but its way more flexible, and easier to retrieve the data you need, without your website having to make necessary table joins.

The fields seller_id and buyer_id in the Property table are both foreign keys linking to the User.id field. Your web app can then count the number of links to a particular user, and use that to autopopulate the housesSold and housesBought fields in the user table.

Relational database design is a huge topic, so I've only scratched the surface, but I hope these are enough pointers to get you going. It'd be worth investing in a good book, perhaps the relevant O'Reilly book for your chosen database platform.

Simon Woolf
  • 603
  • 4
  • 12
  • Responding to your question about user type - just add a "user_type" field to the User table – Simon Woolf Jul 28 '14 at 10:01
  • I see you suggestion but what if I had more types of users such as renter,adminstrator ,visitor etc.. Then the amount of redundant fields would be too much – JmRag Jul 28 '14 at 10:12
  • I wouldn't say so. You might be surprised at how many fields will apply to all types of user. For example "rating" could easily end up being useful for all user types. Having 10-20 fields which may be redundant for some users is much less of a big deal than having to join multiple tables when you don't have to. Joining tables is an expensive operation in db terms = a poorer performing site. – Simon Woolf Jul 28 '14 at 10:36
0

In the table Buyer you can have a column user_id which is going to be a foreign key to the userid of table User. so Buyer: varchar id; varchar housesBought; varchar user_id;

The same applies to the table Seller

Periklis Douvitsas
  • 2,431
  • 1
  • 13
  • 14
  • In this way how can I get the type of a user? I mean lets assume that we have a user with userid=1 and I want to retrieve what he is (buyer/user) what would be the sql question? – JmRag Jul 28 '14 at 09:52
  • If you want to check that a specific user is a buyer you can do the following select * from user where user_id=1 and user_id in (select user_id from buyer). If this query brings you one row then the user is a buyer as well – Periklis Douvitsas Jul 28 '14 at 10:06
0

It has been already said that you could use foreign key to link your buyer and seller tables to the user they represent. As a minor improvement I can propose you to use user_id as primary key for both your seller and buyer tables while it is the foreign key at the same time.

User:
int userid; (Primary Key)
varchar password;
varchar registrationDate;

Buyer:
int user_id; (Primary Key, ForeignKey to User(userid))
varchar housesBought;

Seller
int user_id; (Primary Key, ForeignKey to User(userid))
varchar rating;
varchar housesSold;

It will guarantee that no user can have two seller(buyer) "accounts" and remove unnecessary primary key.

It is so-called identifying relationship.

This and this can show your technical details about how to implement such relationship in DDL.

P.S.: Don't store passwords as plain text.

Community
  • 1
  • 1
Eugene Podskal
  • 10,270
  • 5
  • 31
  • 53
  • How can I store the password? I would pass it through an encryption password and then store it as text. I am using java server faces so can you suggest any good technique in brief? – JmRag Jul 28 '14 at 10:15
  • How to correctly store passwords is very difficult question and surely depends on your technology(does it provide some standard way?). If it is encrypted than it is not so bad as I thought, just don't store decryption key with your program - because storing decryption key with the information it decrypts can be useless and even wrong in many scenarios. Usually password are salted and hashed(multiple times). You may want to read this thread - http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database for some real links and insights. – Eugene Podskal Jul 28 '14 at 10:21