December 21, 2011 at 11:49 pm
Hi
I am designing a database for travel application, it consist Hotel,Flight,Package,Transfers,Cars,Trains as vertical. Identified this as seperate entity and planning to create table for each verticals.In our application there is shopping cart, where customer can search the verticals and add it to the cart.
Another approach is to treat all the verticals as one entity and create one table for example PRODUCT and a column to identify the vericals.
This approach help in the case of development because if new verticals come we don't have to design the new tables. This approach follows the shopping cart where every type of items is stored in one table.
For me the first approach is good, in case of search it will go to the seperate table whereas in the second approach it will go to only one table which causes load to the table and the performance degraded
Please share your views, it would be helpful
Dilip D
December 22, 2011 at 12:42 am
Judging a design as Good or Bad is very difficult because it’s usually a trade-off. If design advantages beets design limitations I would consider it Good Design.
I prefer Flexible Data Model which can adopt business expansion, workflow changes, Change in Hierarchy / Organization Structure, new Products (it’s your case BTW) etc. Thus I like Design#2.
You have to define design priorities for you. Once you set them choice is easy. 🙂
December 22, 2011 at 1:51 am
dilipd006 (12/21/2011)
HiI am designing a database for travel application, it consist Hotel,Flight,Package,Transfers,Cars,Trains as vertical. Identified this as seperate entity and planning to create table for each verticals.In our application there is shopping cart, where customer can search the verticals and add it to the cart.
Another approach is to treat all the verticals as one entity and create one table for example PRODUCT and a column to identify the vericals.
This approach help in the case of development because if new verticals come we don't have to design the new tables. This approach follows the shopping cart where every type of items is stored in one table.
For me the first approach is good, in case of search it will go to the seperate table whereas in the second approach it will go to only one table which causes load to the table and the performance degraded
Please share your views, it would be helpful
Dilip D
A design where you have to create new tables if a new type of product arrives is not really a good design in my opinion.
Normalize your tables and have a ProductType table. There you can add a new line for every new type of product.
Join it to your product table and have a WHERE clause on the ProductType. If you index properly, performance won't really be an issue.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 2:22 am
I think 2 nd approach is not good...it is not a normalized structure
For eg: Consider a table having different drawers we can arrange the file related to verticals in their drawer repectively. In this way we can easily search the content to the coresponding drawer and it is easy to search and faster.
In the second approach we mixed all the vertical files and stored to the drawer , this will result in slow search
December 22, 2011 at 2:37 am
dilipd006 (12/22/2011)
I think 2 nd approach is not good...it is not a normalized structureFor eg: Consider a table having different drawers we can arrange the file related to verticals in their drawer repectively. In this way we can easily search the content to the coresponding drawer and it is easy to search and faster.
In the second approach we mixed all the vertical files and stored to the drawer , this will result in slow search
It's your design. If you want to buy a new drawer every time, be my guest 🙂
I'll just have a nice index that tells me where I can find everything in my drawer 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 2:55 am
Normalization defines a set of methods to break down tables to their constituent parts until each table represents one and only one "thing", and its columns serve to fully describe only the one "thing" that the table represents.
The concept of normalization is the basis on which SQL and relational databases are implemented.
Detail is provided in this link
http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/
December 22, 2011 at 2:59 am
dilipd006 (12/22/2011)
The concept of normalization is the basis on which SQL and relational databases are implemented.
Detail is provided in this link
You got the point. In that sense PRODUCT deserves to be table / entity because it has its own attributes.
December 22, 2011 at 3:02 am
Koen Verbeeck (12/22/2011)
dilipd006 (12/22/2011)
I think 2 nd approach is not good...it is not a normalized structureFor eg: Consider a table having different drawers we can arrange the file related to verticals in their drawer repectively. In this way we can easily search the content to the coresponding drawer and it is easy to search and faster.
In the second approach we mixed all the vertical files and stored to the drawer , this will result in slow search
It's your design. If you want to buy a new drawer every time, be my guest 🙂
I'll just have a nice index that tells me where I can find everything in my drawer 😉
Nice analogy 😉 but why consider fixed / standard drawer only. Why not considering a drawer that allows us to adjust the depth based on file size (cool idea isn't it 😎 )
December 22, 2011 at 3:16 am
dilipd006 (12/22/2011)
The concept of normalization is the basis on which SQL and relational databases are implemented.
Detail is provided in this link
I'm familiar with the concept of normalization.
The design I'm trying to explain is the key-value table:
http://www.devshed.com/c/a/MySQL/Database-Design-Using-KeyValue-Tables/1/
It has it's advantages, but as every other design, also it's own disadvantages.
Do not ask for opinions on two different designs if all you want is confirmation of one particular design.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 3:21 am
dilipd006 (12/22/2011)
The concept of normalization is the basis on which SQL and relational databases are implemented.
And please get your facts straight.
I know you're just quoting the article, but a relational database design is not build upon the concept of normalization. It is build upon the concept of relations. (hence the name of course)
Normalization is a process of organizing data in such a way that redundancy is minimized (and it helps with data consistency et cetera). The key value table can also be normalized (but don't ask me which normal form it has).
It is perfectly acceptable to have a denormalized database design in a relational database, for example in a data warehouse. SQL still works, and it is still pretty performant (even more than in a normalized database, as less joins are used to retrieve data)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 3:23 am
Dev (12/22/2011)
Nice analogy 😉 but why consider fixed / standard drawer only. Why not considering a drawer that allows us to adjust the depth based on file size (cool idea isn't it 😎 )
I consider a fixed drawer, because when the guy who made the drawer has left, I still want to be able to put new files in it 😉 The fixed drawer is built for the future, while dynamic drawers will require constant maintenance 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 3:29 am
Koen Verbeeck (12/22/2011)
Dev (12/22/2011)
Nice analogy 😉 but why consider fixed / standard drawer only. Why not considering a drawer that allows us to adjust the depth based on file size (cool idea isn't it 😎 )I consider a fixed drawer, because when the guy who made the drawer has left, I still want to be able to put new files in it 😉 The fixed drawer is built for the future, while dynamic drawers will require constant maintenance 😀
I am happy you replied but I was pointing to Dilip (bad quote on my part).
If the drawer is designed by another guy I would recommend checking it full very carefully. Who knows he forgot to add locks (security leaks) in it. :hehe: OR You might be lucky one to find his treasures (good design tips) in it. 😉
December 22, 2011 at 3:53 am
Dev (12/22/2011)
Koen Verbeeck (12/22/2011)
Dev (12/22/2011)
Nice analogy 😉 but why consider fixed / standard drawer only. Why not considering a drawer that allows us to adjust the depth based on file size (cool idea isn't it 😎 )I consider a fixed drawer, because when the guy who made the drawer has left, I still want to be able to put new files in it 😉 The fixed drawer is built for the future, while dynamic drawers will require constant maintenance 😀
I am happy you replied but I was pointing to Dilip (bad quote on my part).
Ah ok, now I get it. 🙂 In that case I'm considering the dynamic drawer. And the fixed drawer needs maintenance 😀
(I'm confused by my own analogy)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 22, 2011 at 4:32 am
Ok we r talking too much abt the drawer
this is exactly my scenario,
each product(in this case verticals(Package,hotel,Cars)) had different attributes so this should be treated as different entity.
Hotel attributes:- HotelID,Name,HotelCode,HotelType,StarRating,location,city
Package attributes:- Package ID,Code,Name,No of days,No of nigts,Rating.city,country,price
Cars:- CarID,Code,name,Cartype,pickuplocation,droplocation,price,no of passenger
This is in general ther may be other fields
If we combine all this into one table Product then there are some field which are not common in all table, this column would store null values which will take unnecessary space
December 22, 2011 at 4:53 am
First of all Drawers, you started it & we enjoyed it. Thank you so much (& apology as well).
Before answering your question, I would like to ask few questions. Please cooperate...
If you are asked to store academic details of a candidate for job portal, how will you add this in table?
•Post Graduation?
•Graduation?
•High School?
•Illiterate?
Then Marital Status...
•Married?
•Spouse Name?
•Kids?
If I am student and filling up this form most of the fields would be empty (NULL). Will you consider it a bad design?
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply