April 27, 2016 at 4:32 am
Dear All,
Hope all are doing good.
I have to design table for one small web project, which contains 4 screens. In my old company i was working with .net team every thing as sp , but here java services acts as middleware and they take query or complex sp from me and will put in java code, but table design must be done by me.
Here , I face new issue.
The project basically setting up single Event,which will happen in multiple destination for 1 or more week in each destination, for that setting up hotel, flight ext needed for the members attending event. This is what requirement document says.
But i don't know how to save these information in table
Basically if one event in one destination for 1 week , we can directly insert as one row , but this case here i need to save multiple values for one event in one row.
can you all help me give the best solution.
Thanks in advanbce
April 27, 2016 at 7:13 am
JoNTSQLSrv (4/27/2016)
Dear All,Hope all are doing good.
I have to design table for one small web project, which contains 4 screens. In my old company i was working with .net team every thing as sp , but here java services acts as middleware and they take query or complex sp from me and will put in java code, but table design must be done by me.
Here , I face new issue.
The project basically setting up single Event,which will happen in multiple destination for 1 or more week in each destination, for that setting up hotel, flight ext needed for the members attending event. This is what requirement document says.
But i don't know how to save these information in table
Basically if one event in one destination for 1 week , we can directly insert as one row , but this case here i need to save multiple values for one event in one row.
can you all help me give the best solution.
Thanks in advanbce
Hi and welcome to the forums. You are not going to get an "answer" here because you really haven't asked a question. What you have posted is an extremely vague description of a project that sounds like you are in over your head. I would recommend still using stored procedures as it gives a separation between the business and data layers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 27, 2016 at 8:15 am
I agree with Sean comments. Database design can be something complex without the proper formation. For instance, you're not designing a table, you're designing a database with several tables.
You need to identify entitities and their attributes and relationships. Read at lease the following series of articles http://www.sqlservercentral.com/stairway/72400/ and get some basic knowledge on normalization.
If possible, get some on-site help.
April 27, 2016 at 10:26 am
One thing I would definitely suggest is collecting a list of common questions that you're going to ask of your database, and test your design by trying to answer the questions by writing SQL statements against your sample data. (I was taught to test with maybe 5 rows per table). The idea is to make sure your design is sound and you can get the answers you need. If you can't answer them by writing SQL statements, then your design is likely wrong.
May 6, 2016 at 8:09 am
Sounds like a classic Third Normal Form (3NF) problem. You need to look at the entities and put each one into a separate table with a unique ID (primary Key PK). Where two entities are related then you need to include the ID from one at a link in the other (Foreign Key (FK)). From what you have said - english is clearly not your first language so apologies if I have mis-interpreted something.
Event Entity (event may happen more than once): PK = EventID
Location Entity (locations may be used more than once): PK = LocationID
Event-Location (the event will only happen once in each location)PK = EvLocID, FK = Event.EventID, FK = Location.LocationID
Members (A member may attend more than one event): PK = MemberID
Booking (a booking is made for one member for one instance of an event): PK = BookingID, FK = Event-Location:EvLocID, FK = Member.MemberID
Each entity will then contain relevant information for that entity and only that entity.
For example, The Event entity will contain the Event Name, speaker, cost etc.
The Event-Location will contain the Event Date, the date bookings close etc. IT WILL NOT CONTAIN any location or event details which are not specific to this instance of the event
Of course the above is a gross simplification of what you may get in real life. Events will take place over multiple days, with different topics. The topics may not always be on the same day and they may not always have the same speaker for each instance of the event. Your Entity model must reflect the real-world scenarios or you will end up with horrible data storage and middleware code to make sense of what is being recorded.
good luck.
June 24, 2016 at 12:06 am
Thanks a lot Mr or Mrs 500 , thanks for the solution. I designed like you instructed :-):-):-)
June 24, 2016 at 1:53 pm
you are very welcome. Well done on setting off on the right track; you have saved a load of pain later on
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply