Database design tips for a water supply system.

I am working on a government project which involves building a backend logging and billing system for water supply management. Usually the government has two types of customers - Industries and colonies. My main concern is how to place these two types of customers in database.

The difference between these two customers is the information. For example, the industries need to provide more information such as GST number, industry code etc. And the charges for water usage are applied differently based on the customer type - colony or industry.

Now followings are some points I am considering before designing.

1 - If I use two tables for different customer types then it might be slightly difficult to implement single authentication system. I don't think that would be a good practice.

2 - If I place both customers in one table with a customer_type column, then there will be many NULL cells. For example colony customers do not need to give their GST number.

3 - There are many tables where the reference for a customer is required. For example in water_connections table. If I place both customers in different tables, then I will have to use concept of polymorphism in many tables.

I am hoping that one of you would suggest some good practices for such scenario.

Tray2

5 years ago

Level 73

Use one customer table.

There you can if you need have a customer type to differentiate between the two kinds of customers.

The common fields should be placed in the cutomers table the other fields that are not common can be placed in a seperate table(s).

Either you do it as a key value pair one table

customer_id
key
value

the plus on doing that is that you can have both customer types in the same table. The downside is that it's a little bit more tricky to query on those keys.

Or create it as a normal table with the fields needed for the customer type. Something like

industrial_customers_info
colony_customers_info

1 like

raulmercadox

5 years ago

Level 1

I suggest that the best obtion is to use only one table. Let's say client. Then for the fields that are specific to one type of customer create another table that references to the main one. Use foreign keys for that.

martinbean

5 years ago

Best Answer

Level 80

@ravish There’s nothing stopping you having a polymorphic Customer model, and then a customer having many users. This way, a user logs in and is assigned a customer, and can only view information about customers they are assigned to.

Your Customer model would hold columns common to all customers (so say, name), but have a polymorphic relation to either an Industry or Colony model. These models can then have the fields they need, i.e. GST number if an industry.

gitwithravish

5 years ago

Level 16

@martinbean It make sense. But I am wondering wouldn't it be complicated if ever I need to run raw queries and joins to the database or while giving direct database access to third party ? There are chances that we might run into such situations. Since the entire design of polymorphic concept reply on eloquent API, wouldn't it be tricky and complex to work with the database where we cannot make use of existing eloquent models in our project ?

Please or to participate in this conversation.