Design question asp.net c#

Question

Hi Experts,

I am working on a website where I have many lookup values like cities, states, countries car makes, models and many more. there are alwasy two fields for these values ID and Description. Now I have a question, should I keep them in one table and keep some TypeId kind of field which differentiate them or should they stay in differnt tables.

Also how would be my Dataprovider class, currently I am thinking to have one class which can provide all the data. But, I feel there will be many methods in this class.

Suggestions will be welcome.

Regards Parminder

Answer 1

I would prefer you putting them in separate tables for the ease of differentiation.

As for the dataprovider class is concerned, I recommend you using an ORM, instead of deciding how to write your dataprovider class.

Answer 2

Really depends, if you are selling products I would say yes keep them in one table and have some sort of TypeID to distinguish the type of product. For instance, if you are selling items your TypeID could be Automotive, Education, etc.

However, if you are dealing with different entities that serve a specific purpose and each entity doesn't really relate to one another then seperation is good. We need more info to help you :).

Dataprovider could be one class namely your data access layer, and it doesn't matter that you may have a lot of methods; that is quite normal.

Answer 3

cities, state, countries are seems to be different entities.

so its better to keep those details as seperate master table.

if there are any relation then relate them using foreign keys.

which will reduce the future overheads if there is going to be any change or updation and also to reduce the load to DB if these are all in one table.

Write a Seperate DBProvider to execute SP, adhoc queries

and also create seperate BusinessModel representing the each table which in terms interact with the DBProvider and the front end of the client.

That should be good and perfect design strategy.

Answer 4

It depends on the scenario and domains for these entities. Lets say at design time if you are not aware of the type of entities you will be having then TypeId approach is better so that when a new type is added you don't have to create another entity for it. However if during desing time you are sure of the entities domain (What are the entities you'll be having) then keep them in separate tables so that your search can be more efficiently optimized.

However, do consider what if the attributes for an entity need to be updated (added, edited, removed etc), so if you keep them in one table the update may affect all the others. And what if later on you need to add a relation with or to your table. All the unrelated entities may also inherit the relationship.

Answer 5

Regarding your Dataprovider class - if it gets too big, this could be considered a code smell which is generally thought of as a bad practice. Deciding how big is too big is somewhat subjective.

From the article:

Large class: a class that has grown too large, see God object.

Answer 6

Always put lookups in their own tables so that you can have meaningful foreign keys on related tables for relational integrity (ie CountryID in Customer table references CountryID in Country table).

Your lookup schema may diverge from the standard ID / Description on a per-entity basis as well, so 'one-size-fits-all' is not the best way to start.

Also, have one repository / data provider per lookup, don't mash them all into one big class as that will get unwieldy and untesty.

Answer 7

Trust me, the time will come when someone says, "hey, can we add latitude/longitude to cities", and you'll be kicking yourself because those properties make no sense with car makes and models.

You can't really go wrong with physically separating unrelated, disjoint entities. So I'd recommend different tables.

Answer 8

I've seen it done both ways, but in terms of discoverability, I much prefer having a separate table for each piece of data. It's not that difficult to maintain the multiple tables, and it makes things much easier down the road when you decide you need to add a column or two to one of the items.

Answer 9

If you think that over time the different types of data will acquire different properties, then put them each in their own table. However, if you reckon they're all always going to stay as just Id and Description then, for simplicity, put them all in the same table with a TypeId.

I agree with the other answers that advise using a third-party data access or OR/M component, like LINQ-to-SQL, NHibernate, SubSonic, LLBLGen Pro, etc.