Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
webforces
Starting Member
6 Posts |
 Posted - 2014-07-23 : 07:07:47
|
| • Hi,This may be a little of an open ended question, but Id like to ask the crowd as I have a delema to whether or not to use a standard database design or a propertybag/flatfile format and transform the information within my application?THE SITUATION (EXAMPLE)We have a table called "people". And these persons can either be a basic contact or be associated to several other "types" such as a member, a representative, a staff member with login details etc. These types are expected to expand and may need additional fields adding to them.A: The old fashioned poor approach is to create a table with all the unused columns and simply ignore what we don't need.B: After normalisation, the traditional approach would be to have link table and each "type" would have its own table with the separate columns needed.C: Lasty, we have recently done some projects using matrix and property type lookup tables (e.g. a reference table to hold the persons ID, then the columns held in a single properties table of "propertyname and proteryvalue". When the application "gets" the record it can return a completely dynamic length column list based on how many properties it finds. I.e. Person 1 have only their email address and name in the property bag so the datatable returned in code would be 2-3 columns (inc id). Person 2 has lots of properties and so their datatable would be returned in code with many more columns.I hope that makes sense?THE DILEMAIf I was only using our application to access the data then I could setup a function library to control the access, but I asl want to use report editors (maybe SQL report Services?). And the customer would get completely confused to know which field is what and how to get the data out? I also want to avoid situations where the application expects someone to have a property (column) and its not there? It may make a difference if I tell you that they will aslo have tables holding heiracrchical groupings too, which will already make it hard for the customer to use in SQL Report Services. Should I use the traditional approach and the metastable (property bag) approach?PROS AND CONS of B:+Traditional approach and most tools that need to access SQL directly will work, assuming the customer understands the column names properly-A single field change would likely involve all levels of MVC (model, Controller, View) so 3 times the work (maybe 3 people if using specialist SQL Admin, C# Dev and Interface Designer)-New person types require a whole new table to be linked in-Global searches need to constantly be updated to include other tables columns+Our development suite (Visual Studio) allows more visual designing in the IDE when the table is fixedPROS AND CONS of C:-Cant see the record easily in SQL directly-Would have to make any report editor use datasets within an the app rather than direct to SQL directly, making the whole system dependant on a proprietary APP control system (although we would control this anyway)+Global searches are very good, as all properties are held in a column-The property table would grow quote big, so needs good indexing+Any new fields or field groups (types) would simply be a configuration setting in the app (not any manual admin)+If any presentation of data changes where needed this is likely to only require the controller (developer) on MVC to input changes (assuming the interface expects dynamic fields)-The tables to show to the user through the app would need to handle dynamic columns and so would need to have generic auto formatting in code (which we could not see in the designer (Visual Studio) and set exactly as required). Slight guess work, but we could hold setting in the property bag to indicate how to show that property.A little bit "war and peace", but your thoughts? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-23 : 10:53:06
|
| First off, build views for your customers to hide the implementation details. Then you can change the implementation if you need to. Also using views will hide things from MVC as well, though it might be better to build stored procedures for MVC use. Again, the implementation details are hidden. Why do you say "A single field change would likely involve all levels of MVC (model, Controller, View) so 3 times the work "The whole point of MVC is to separate those concerns. The database changes should only affect the Model, if the application is designed correctly.If your Context Manager is directly tied to the database structure, changing the latter means changing your application. NOT A GOOD THING! |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-07-23 : 13:34:15
|
quote:
Originally posted by webforces[brA little bit "war and peace", but your thoughts?
If we have learned nothing from War Games, it should be that the only winning move is not to play. :)But seriously, it kind of depends on your goal. I too have seen all these types of things implemented. If you want a solid data base, don't the bag/EVA (entity attribute value) model. If you want to use the DB as a "data storage file" that makes it super easier for anyone to add an attribute, then use the bag/EVA model. |
|
|
|
webforces
Starting Member
6 Posts |
Posted - 2014-07-23 : 17:49:00
|
| Excellent feedback. Thank you. We are not using a formal MVC approach, just separating out the technical roles for the work mainly. The front will be DevExpress to speed up the look and feel so it maybe more like MMVC or whatever the terms are.I didn't think about using SQL views, I haven't had much exp with that side of SQL being more of a VS Coder. As this is going to be a MIS system I think I look to make it relational to avoid headaches with other tools trying to access data. That way I can enforce some integrity.My reading of MVC maybe a little weak, however I thought that if I changes a field in the MODEL. And I want to leave the Interface design to non-coders that would require a VIEW change. And if the field needed to be used in some what it would require CONTROLLER? Maybe I am barking up the wrong tree? A little annoyed that DevExpress doesn't yet support HierachyID as that looks much better than ParentIDs! Im going to have a EVA table anyway to keep custom vars etc, but now you know the way im going, does it sound sensible to do all the common processing in SQL (as stored proc or otherwise) or to just use the tables raw into the front end code (DEVEXPRESS) and do any process in application code? YOu both sound like you know your stuff, so thanks in advance. |
|
|
|
webforces
Starting Member
6 Posts |
Posted - 2014-07-23 : 18:00:46
|
| May not have made much sense at the end there. To re-iterate and expand.I intend to have a number of interface applications to view this data, say desktop app, web app and webservice to native tablet devices etc.So I need a central gateway to handle the format of views and not have to format and change for every device.Therefore, would it be better to set all the data ready to display with formatting in the output from SQL or create a common formatting-view library in code (DAL) that I might use cross platform. I was thinking of making all the applications (inc the desktop) use the webservice API and keep the common library there. But if you suggest SQL is better I may think again? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-24 : 08:11:37
|
| If you're thinking of MVC as a design pattern, then the Model would project the data in a format for the View to consume it. The Model does not have to reflect the design of the database tables underneath it (indeed, a Model doesn't require a database at all). If the database changes, the Model layer must change so that it continues to project the same results for the View to use. This gives you separation of responsibilities. The Controller and View components can go on their merry way completely oblivious to the database design or what the Model does to expose the data the C and V use (unless you are adding functionality or want to View more data, in which case the View may change). The Controller may need to know about fields in the View in order to direct the Model to do its thing, but doesn't need to know how the Model works or anything about any persistent storage (or even whether that exists!) supporting the ModelBTW,"enforce some integrity" is best done at the database level, with appropriate constraints (PKs, FKs, Unique Indexes, Check constraints, etc). Let SQL maintain the integrity for you. It's really good at it! |
|
|
|
webforces
Starting Member
6 Posts |
Posted - 2014-07-24 : 14:08:06
|
| Thanks to both of you. I did some VIEW reading up and that's the ticket (with INSTEAD OF triggers), Cheers guys.... |
|
|
|
webforces
Starting Member
6 Posts |
Posted - 2014-07-24 : 14:14:29
|
| One last question, slightly off the question but still in the right topic area. I have a table called people. People will have core columns that you need to exist in the database. E.g. a name etc. But we will then have several 1-to-1 relationships with additional people information, such as Employer, Payment details etc. Is it best to build a single big table for this or is there a way to reference these in a separate table as 1-to-1 without creating more work to administer (mainly so we can logically separate groups of columns in our mind.... i.e people_contacts and people_paymentdetails and people_employer_details)Ta in advance and appreciate you helping me get going with all this (I will return the favour to the forum once I am competent ;-) |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-24 : 14:38:33
|
| I'm sure you want to normalize your data into People, Employers, Payments tables etc. The People table would have a FK-PK relationship. e.g. one People record might have an EmployerId column that is an FK to the Employer(EmployerId) column. I suggest you work through Stairways to Database Design (http://www.sqlservercentral.com/stairway/72400/) as a good introduction to the topic. |
|
|
|
webforces
Starting Member
6 Posts |
Posted - 2014-07-25 : 06:30:34
|
| Up to lesson 7 BTW. Could I ask another opinion question, this time related to security access, DCL and Proprietary access rights.I have a people table that have roles and certain access to groups (and any subgroup) info in 3 tables (related through link tables).My question is: Should I use the overall SQL security to add their logins at that level (and can I automate this via store proc etc if a member of staff needs to add another? I see this as a security risk at the moment and like a simple single login for the client to the database that has read and write limits only.OR should I use views with a DCL (Access) list?OR just keep with my original idea of holding those permissions of access as a property set within my own database logic?PS: Just made my own CLR procedure. V exciting.PPS: Just asking, but do you freelance? I would love to get somebody I trust to occasionally look over (and advice) for the new databases I set up now and then. All of them are small (this is our biggest to be fair) as we are only a very small company, and would love a guru to lookover the build process before we commit everything. Is this of any interest to you? Apologies if this is not the place to ask. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-25 : 08:13:07
|
| Database security is a big topic, but in a nutshell:1. Use Database Schemas to group tables together and always use a 2-part identifier (e.g. in database MyDatabase, define a schema MySchema, put MyTable in that schema and always write 'SELECT ... from MySchema.MyTable'2. Determine roles for access to the schema and tables within (e.g. administrator, read-only user, read-write user etc. and give them meaningful names!)3. Create or use corresponding Active Directory groups and add the groups to the roles you just createdFrom that point on, you can control access to your db by AD group membership.There's another SQL Stairway that is a good start: http://www.sqlservercentral.com/stairway/110890/There are lots of other guides and tutorials on Security (and don't forget BOL) when you need more. |
|
|
|
|
|
|
|
|
Traditional Approach Vs Property Bag Tables Advice